ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Runtime error (https://www.excelbanter.com/excel-programming/382652-runtime-error.html)

Martin
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

Corey
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

NickHK
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

Martin
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

Martin
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

Dave Peterson
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

Tom Ogilvy
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


All times are GMT +1. The time now is 12:39 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com