Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)"
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 Befo=Workbooks("Data.xls").Sheets(1) Counter = Counter + 1 Wend End With Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub ------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)"
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" 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- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple file uploading - runtime error'13': type mismatch "WhileCounter <= UBound(FName)"
That would work if the multiselect:=true wasn't there.
if isarray(fname) = false then exit sub should work. Jim Cone wrote: 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" 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)"
On Mar 1, 8:54 am, Dave Peterson wrote:
That would work if the multiselect:=true wasn't there. if isarray(fname) = false then exit sub should work. Jim Cone wrote: 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" 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- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
%1 appears in "Application used to perform action" when trying to edit a file type | Excel Worksheet Functions | |||
When I type "13" the spreadsheet shows "14." Possibilities? | Excel Discussion (Misc queries) | |||
Where is the toolbar with the "bold type", "font type", options | New Users to Excel | |||
"IF"- "THEN" type Formula based on Null value | Excel Worksheet Functions | |||
Runtime Error 13 - type mismatch | Excel Discussion (Misc queries) |