Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA "1004" error when .Refresh BackgroundQuery:=False | Excel Programming | |||
VBA Find produces 1004 error | Excel Programming | |||
runtime error 1004 saveas text file | Excel Programming | |||
Error 1004 in printing .ps (postscript) file from .xls file | Excel Programming | |||
Error 1004 when I open a file | Excel Programming |