Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error
I still cannot solve my problem after trying a few suggestion from the
experts out here. I still have runtime error 13 with myFileName(i) even If I put both in the IF function. Another other suggestions? My code as follows: Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error
Will this do it willout error :
Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub On Error Resume Next ' <===== Here Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub "Martin" wrote in message ... I still cannot solve my problem after trying a few suggestion from the experts out here. I still have runtime error 13 with myFileName(i) even If I put both in the IF function. Another other suggestions? My code as follows: Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error
Martin,
You need to determine if you are accessing the elements of the array or the Variant/array in general. Sub Import() Dim myFileName As Variant Dim i As Long 'Here we assign the return value of GetOpenFilename to the variant "myFileName" myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) 'Did the user cancel ? i.e. Is the variant an array ? If IsArray(myFileName) = False Then Exit Sub 'Now we know we have an array, so loop through all elements For i = LBound(myFileName) To UBound(myFileName) 'Now we need to access each element in the array, with the index "i" With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFileName(i), _ Destination:=Range("A1")) 'etc NickHK "Martin" wrote in message ... I still cannot solve my problem after trying a few suggestion from the experts out here. I still have runtime error 13 with myFileName(i) even If I put both in the IF function. Another other suggestions? My code as follows: Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error
It will not work, the following will be highlighted in yellow with the
runtime error '13' type mismatch message. My objective is to enable to select multiple files at one click for import. With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) "Corey" wrote: Will this do it willout error : Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub On Error Resume Next ' <===== Here Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub "Martin" wrote in message ... I still cannot solve my problem after trying a few suggestion from the experts out here. I still have runtime error 13 with myFileName(i) even If I put both in the IF function. Another other suggestions? My code as follows: Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error
Finally it works!!! :) Thanks Nick for your great help in this few days.
"NickHK" wrote: Martin, You need to determine if you are accessing the elements of the array or the Variant/array in general. Sub Import() Dim myFileName As Variant Dim i As Long 'Here we assign the return value of GetOpenFilename to the variant "myFileName" myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) 'Did the user cancel ? i.e. Is the variant an array ? If IsArray(myFileName) = False Then Exit Sub 'Now we know we have an array, so loop through all elements For i = LBound(myFileName) To UBound(myFileName) 'Now we need to access each element in the array, with the index "i" With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFileName(i), _ Destination:=Range("A1")) 'etc NickHK "Martin" wrote in message ... I still cannot solve my problem after trying a few suggestion from the experts out here. I still have runtime error 13 with myFileName(i) even If I put both in the IF function. Another other suggestions? My code as follows: Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error
If only you had read your replies to your first post!
Martin wrote: I still cannot solve my problem after trying a few suggestion from the experts out here. I still have runtime error 13 with myFileName(i) even If I put both in the IF function. Another other suggestions? My code as follows: Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error
Actually, he had two previous posts to this thread.
but as you implied, in the previous thread both you and kounoike gave different solutions where either would have worked. -- Regards, Tom Ogilvy "Dave Peterson" wrote: If only you had read your replies to your first post! Martin wrote: I still cannot solve my problem after trying a few suggestion from the experts out here. I still have runtime error 13 with myFileName(i) even If I put both in the IF function. Another other suggestions? My code as follows: Sub Import() Dim myFileName As Variant myFileName(i) = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0, True) If myFileName = False Then Exit Sub Dim i As Long For i = LBound(myFileName) To UBound(myFileName) With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;" & myFileName, _ Destination:=Range("A1")) .Name = "smartscope" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Next End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
runtime error: syntax error or access violation | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |