1004 error - can't find text file to refresh ...
 

I have 2 procedures in a module. One imports external data (.csv) to a named
sheet (which works) then calls another procedure wthich imports a .dat file.
I'm getting the error above during the second import. I have checked using
debug.print and the file path is correct. The last line of code (.Refresh...)
is causing my problem. Here's my code. Any ideas?

Thanks
LeAnn

CODE:
Sub ImportData(strFile As String)
'

Dim strDir As String
strDir = "my network path" & strFile

With Worksheets("Result")
.Select
.Range("a1").Select
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strDir _
, Destination:=Range("Q22"))
.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 = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

1004 error - can't find text file to refresh ...
 

I think the problem is based on the data you are trying to import. Not sure.
Try go on spreadsheet to tools - Macro - Record Macro. Then manually import
each of the two files. On spreadsheet Menu - Data - Import External Data -
New web query. Enter in URL address the location of the two files. The URL
can be a file on your disk. Just enter c:/temp/... . Use forward slashes
instead of backward slashes. Then stop recording macro and compare the macro
for importing the two files. You can actually run the macro again to make
sure the recorded macro works properly.

"LeAnn" wrote:

I have 2 procedures in a module. One imports external data (.csv) to a named
sheet (which works) then calls another procedure wthich imports a .dat file.
I'm getting the error above during the second import. I have checked using
debug.print and the file path is correct. The last line of code (.Refresh...)
is causing my problem. Here's my code. Any ideas?

Thanks
LeAnn

CODE:
Sub ImportData(strFile As String)
'

Dim strDir As String
strDir = "my network path" & strFile

With Worksheets("Result")
.Select
.Range("a1").Select
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strDir _
, Destination:=Range("Q22"))
.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 = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

1004 error - can't find text file to refresh ...
 

Good suggestion - I've been busy but I'll give a try soon and let you know.

Thanks

"Joel" wrote:

I think the problem is based on the data you are trying to import. Not sure.
Try go on spreadsheet to tools - Macro - Record Macro. Then manually import
each of the two files. On spreadsheet Menu - Data - Import External Data -
New web query. Enter in URL address the location of the two files. The URL
can be a file on your disk. Just enter c:/temp/... . Use forward slashes
instead of backward slashes. Then stop recording macro and compare the macro
for importing the two files. You can actually run the macro again to make
sure the recorded macro works properly.

"LeAnn" wrote:

I have 2 procedures in a module. One imports external data (.csv) to a named
sheet (which works) then calls another procedure wthich imports a .dat file.
I'm getting the error above during the second import. I have checked using
debug.print and the file path is correct. The last line of code (.Refresh...)
is causing my problem. Here's my code. Any ideas?

Thanks
LeAnn

CODE:
Sub ImportData(strFile As String)
'

Dim strDir As String
strDir = "my network path" & strFile

With Worksheets("Result")
.Select
.Range("a1").Select
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strDir _
, Destination:=Range("Q22"))
.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 = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub