use variable as connection in query table?

[Kieran1028]

Hi,

I'm trying to write a macro that will prompt for a filename using th
.getopenfilename method, then use that filename path as the connectio
in a .querytable.add function. I've tried this:

Sub Import()
Dim fname as Variant
FName = Application.GetOpenFilename("Text files(*.TXT),*.TXT")
With ActiveSheet.QueryTables.Add(Connection:="TEXT;FName", _
Destination:=Range("A1"))
.Name = "ND2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 12085
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = """"
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


However, this macro always returns an error when it gets to th
.refresh backgroundquery line... it' can't find the txt file. So,
figure I must be referencing the variant FName incorrectly in th
querytable.add function's connection statement.

Any help here?

Thanks,
-Kiera

 

[crispbd]

-Try changing:-
With ActiveSheet.QueryTables.Add(Connection:="TEXT;FName", _
Destination:=Range("A1"))

-To-

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName, _
Destination:=Range("A1")