Runtime Error 13 help

M

Martin

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
 
P

paul.robinson

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
Click to expand...

Hi
You might need
If myFileName = "False"

myFileName returns the text "False" if Cancel is pressed, not the
Boolean FALSE.
regards
Paul
 
M

Martin

Hi Paul,

By changing If myFileName = "False", I got the error message compile error,
syntax error.
 
N

NickHK

Paul,
You sure about that ?

<From Help>
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If
</From Help>

And in the Immediate window, pressing Cancel:
?typename(Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, 0, 0,
True))
Boolean

Martin,
Whilst you are testing if the user cancelled, you are not doing anything
about it e.g. exitting the sub. So you trying to loop on a boolean value,
hence the type mismatch.
Use:
If myFileName = False Then Exit Sub
and delete the End If towards the bottom of you routine.

NickHK
 
K

kounoike

How about using something like "If VarType(myFileName) <> vbBoolean Then"
insted of using "If myFileName = False Then"

keizi
 
D

Dave Peterson

Try:
If IsArray(myFileName) Then


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
Click to expand...
 
M

Martin

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
 
N

NickHK

Martin,
Yes, because you forgetting that you have an array, so you need to access
the elements individually. e.g.
myFileName(i)

NickHK
 
M

Martin

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.
 
N

NickHK

Martin,
I did tell you. Use this:
myFileName(i)
instead of :
myFileName

NickHK

Martin said:
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.
Click to expand...
 
M

Martin

I still have runtime error 13 with myFileName(i) even If I put both in the IF
function.


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with VBA Macro needed 3
Runtime error 6
VBA for Importing Text Files 6
Help in Getting the File Name from a path 3
User Select File on Import External Data 3
.Refresh BackgroundQuery:=False 3
Imported text file to begin from the first empty cell 3
SOS:why run error 3

Top