Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data, Import Text File, File name problem
I recorded a macro to import a text file using Get External Data,
Import Text File. It works fine until I went to change the name of the file I am trying to import, to a variable that gets created when the macro is run. It hangs up on the last line: .Refresh BackgroundQuery:=False it says that: Excel cannot find the text file to refresh this external data range. Check to make sure the text file has not been moved or renamed, then try the refresh again. I am creating this text import fresh every time so I am not trying to update a existing query with data from a different file. The file name that I am importing the data from is: \\prowler\ftpshare\mfg\GT_GAGERPT_030710.CSV If I put this text in directly for the variable "Report" in the macro it all works fine. It is just when I pass it this variable. Do I have a problem with leaving my variable a Variant type or some other problem? Thanks for any help. Scott Here is my code: Sub CreateReports() a=\\prowler\ftpshare\mfg\GT_GAGERPT_030710.CSV gagereport(a) End Sub Sub GageReport(Report) Sheets("GagesDue").Select With ActiveSheet.QueryTables.Add(Connection:="text;Repo rt" _ , Destination:=Range("A1")) .Name = "GT_GAGERPT_030626" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False End With End sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data, Import Text File, File name problem
One obvious problem is
a=\\prowler\ftpshare\mfg\GT_GAGERPT_030710.CSV should be a="\\prowler\ftpshare\mfg\GT_GAGERPT_030710.CSV" but even if that is correct (and putting it in an email removed the "" Connection:="text;Report" _ should be Connection:="text;" & Report _ Regards, Tom Ogilvy "Scott Riddle" wrote in message om... I recorded a macro to import a text file using Get External Data, Import Text File. It works fine until I went to change the name of the file I am trying to import, to a variable that gets created when the macro is run. It hangs up on the last line: .Refresh BackgroundQuery:=False it says that: Excel cannot find the text file to refresh this external data range. Check to make sure the text file has not been moved or renamed, then try the refresh again. I am creating this text import fresh every time so I am not trying to update a existing query with data from a different file. The file name that I am importing the data from is: \\prowler\ftpshare\mfg\GT_GAGERPT_030710.CSV If I put this text in directly for the variable "Report" in the macro it all works fine. It is just when I pass it this variable. Do I have a problem with leaving my variable a Variant type or some other problem? Thanks for any help. Scott Here is my code: Sub CreateReports() a=\\prowler\ftpshare\mfg\GT_GAGERPT_030710.CSV gagereport(a) End Sub Sub GageReport(Report) Sheets("GagesDue").Select With ActiveSheet.QueryTables.Add(Connection:="text;Repo rt" _ , Destination:=Range("A1")) .Name = "GT_GAGERPT_030626" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False End With End sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import External Text Data and Refreshing Problem | Excel Worksheet Functions | |||
Excel 2007/Vista: Missing file name on import external data connection | New Users to Excel | |||
Import External Data Source File Location Changed | Excel Discussion (Misc queries) | |||
How can you import external text file paragraph a into single cell | Excel Discussion (Misc queries) | |||
import external data from changing file name | Excel Worksheet Functions |