Hi,

I'm trying to use a variable to import a text file. Is it possible to change the path and filename below to a variable? I would like to change "L:\GEAM Data.txt" to a variable "fname", which a user previously entered using Application.GetOpenFilename.

With ActiveSheet.QueryTables.Add _
    (Connection:="TEXT;L:\GEAM Data.txt", _
    Destination:=Range("A1"))

Any help would be greatly appreciated as I've tried every syntax combination I could think of.

Thanks,
Mike

Recommended Answers

All 5 Replies

If I'm not mistaken (yeah, sometimes, but not frequently ;) ) this should work:

Fname = Application.GetOpenFilename
With ActiveSheet.QueryTables.Add (Connection:="TEXT;" & Fname, Destination:=Range("A1"))
end with

Now, I've tried the code, and maybe my excel VBA Coding is a bit rusty... but I don't get it loading into any cells that way. I think the file will have to be opened through code, read in, and put into the cells..... but let me know if the above works for you, and maybe I can figure out why it doesn't for me :eek:

It worked for me! Here's the total code (with your change) that worked. Thanks much!!!!

Dim fname As String
fname = Application.GetOpenFilename
      
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=Range("A1"))
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .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 = False
     .TextFileSpaceDelimiter = False
     .Refresh BackgroundQuery:=False
End With

Sweet! ;)

Not really wishing to drag up the past but this little snipit of code is what i have been looking for all day, thanks very much.

in case user press cancel,
put this code:
if fname= false then exit sub

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.