Get External Data, Import Text File, File name problem

[Scott Riddle]

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;Report" _
, 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

 

[Tom Ogilvy]

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]

Thanks Tom, Connection:="text;" & Report _
worked perfectly.

Scott Riddle
John Deere, Ottumwa IA

 

[JFLord]

I have a similar problem but this solution did not worked out
here is my code

Sub ImportationH02()
Dim ddate As Date
ddate = [LaDate]
'Importation H02
Workbooks.Add
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;W:\RCEE\Backup\H02_intrants_CCD_SUN_Initial_" & Format(ddate, "yyyy-mm-dd") & "_0306_xml.p_rcee_q" _
, Destination:=Range("A1"))
.Name = "H02_intrants_CCD_SUN_Initial_" & Format(ddate, "yyyy-mm-dd") & "_0306_xml.p_rcee_q"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 932
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With