S
Sinner
Hello
Below is a code to upload multiple delimited text files in to single
workbook in multiple sheets.
When I run it, a dialog box for file selection opens from where I can
goto my files and select one or multiple files. Problem is that when I
press cancel or close the dialog to return back without selecting any
file, it gives a runtime error'13': type mismatch. When I debug, it
refers to error in the following line
While Counter <= UBound(FName)
Any idea??
Thanks
-----------------------------------------------------------
Full code is:
Sub Import_File()
'This routine Prompts the user to highlight file/files to be imported
and then
'imports the 'file/files into a separate Excelworksheet and places all
the
'worksheets into the same workbook.
'Title to be shown in the Dialog Box
Const iTitle = "Click on file to Import (hold down CTRL key to choose
multiple files)"
'File Types to list
Const FilterList = "Text Files (*.txt*), *.txt*, All Files (*.*),
*.*"
'Variables
'Looping variable
Dim Counter As Integer
'Filename
Dim FName As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Application
'Get the Filename/names. If multiple files are selected, the filenames
are returned as an array
FName = .GetOpenFilename(Title:=iTitle,
FileFilter:=FilterList, _
FilterIndex:=1, MultiSelect:=True)
Counter = 1
'While there are still files, assign the filename to the array
While Counter <= UBound(FName)
'File import specifications
Workbooks.OpenText Filename:=FName(Counter), Origin _
:=437, StartRow:=9, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
SemiColon:=False, Comma _
:=True, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2),
Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 2), Array(9, 2),
Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2))
_
, TrailingMinusNumbers:=True
'Format the worsheet
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.Zoom = 85
'Move the Sheet into same workbook.
ActiveSheet.Select
ActiveSheet.Move Before:=Workbooks("Data.xls").Sheets(1)
Counter = Counter + 1
Wend
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
-------------------------------------------------------------------
Below is a code to upload multiple delimited text files in to single
workbook in multiple sheets.
When I run it, a dialog box for file selection opens from where I can
goto my files and select one or multiple files. Problem is that when I
press cancel or close the dialog to return back without selecting any
file, it gives a runtime error'13': type mismatch. When I debug, it
refers to error in the following line
While Counter <= UBound(FName)
Any idea??
Thanks
-----------------------------------------------------------
Full code is:
Sub Import_File()
'This routine Prompts the user to highlight file/files to be imported
and then
'imports the 'file/files into a separate Excelworksheet and places all
the
'worksheets into the same workbook.
'Title to be shown in the Dialog Box
Const iTitle = "Click on file to Import (hold down CTRL key to choose
multiple files)"
'File Types to list
Const FilterList = "Text Files (*.txt*), *.txt*, All Files (*.*),
*.*"
'Variables
'Looping variable
Dim Counter As Integer
'Filename
Dim FName As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Application
'Get the Filename/names. If multiple files are selected, the filenames
are returned as an array
FName = .GetOpenFilename(Title:=iTitle,
FileFilter:=FilterList, _
FilterIndex:=1, MultiSelect:=True)
Counter = 1
'While there are still files, assign the filename to the array
While Counter <= UBound(FName)
'File import specifications
Workbooks.OpenText Filename:=FName(Counter), Origin _
:=437, StartRow:=9, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
SemiColon:=False, Comma _
:=True, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2),
Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 2), Array(9, 2),
Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2))
_
, TrailingMinusNumbers:=True
'Format the worsheet
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.Zoom = 85
'Move the Sheet into same workbook.
ActiveSheet.Select
ActiveSheet.Move Before:=Workbooks("Data.xls").Sheets(1)
Counter = Counter + 1
Wend
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
-------------------------------------------------------------------