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:
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
Thanks for any help.
Here is my code:
With ActiveSheet.QueryTables.Add(Connection:="text;Repo rt" _
.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)