multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)"

[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
-------------------------------------------------------------------

 

[Jim Cone]

If you cancel the dialog then you have to exit the sub or it
continues on until it errors. Add this line before "Counter = 1"...
"If FName = False Then Exit Sub"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Sinner" <[email protected]>
wrote in message
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

-snip-

 

[Dave Peterson]

That would work if the multiselect:=true wasn't there.

if isarray(fname) = false then exit sub

should work.

 

[Sinner]

That would work if the multiselect:=true wasn't there.

if isarray(fname) = false then exit sub

should work.









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dear Jim & Dave,

Thanks for your reply.
Jim it works for single file. Thanks.
Yup dave its a multi select. It works

Grateful to both of you.
Godbless.

Stay in touch.