Runtime error

[Martin]

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

 

[Corey]

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

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

 

[NickHK]

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]

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"))

 

[Martin]

Finally it works!!! Thanks Nick for your great help in this few days.

 

[Dave Peterson]

If only you had read your replies to your first post!

 

[Tom Ogilvy]

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.