Runtime Error 13 help
Hi Nick,
Sorry, I really dunno anything about VBA so I don't understand what is wrong
with the code and the array. This code is given to me by somebody last year
when I posted some help on VBA code on Looping the Import function. I will
very much appreciate if you can help in my code. Thanks a lot.
"NickHK" wrote:
Martin,
Yes, because you forgetting that you have an array, so you need to access
the elements individually. e.g.
myFileName(i)
NickHK
"Martin" wrote in message
...
All the above cannot. Now it is highlighted yellow at the following code
area:
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))
My code is:
Sub Import()
Dim myFileName As Variant
myFileName = 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" wrote:
Try:
If IsArray(myFileName) Then
Martin wrote:
I got the Runtime Error 13 Type mismatch error when I modified my VBA
to the
following in order to select multiple files to be imported. What
should be
the correct code? Thanks.
Sub Import()
Dim myFileName As Variant
myFileName = Application.GetOpenFilename("Text Files
(*.txt),*.txt",
0, 0, 0, True)
If myFileName = False Then
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 If
End Sub
--
Dave Peterson
|