Import text file name

[BernieRS]

I used the wizard to import data from a text file and set the External Data Range Properties for saving the query, prompting for file name on refresh, and filling down formulas, etc. Is there some way of capturing the name of the file last imported as the source of the current data for displaying in a cell on the sheet? I use a button macro containing Range("B4").Select, Selection.QueryTable.refresh BackgroundQuery:=False to import the data. Thanks. --Bernie

 

[BrianB]

This might do. It looks for "FROM" in the Sql string. May need to adapt further for anything after the file name :-

    Dim MySql As String
    Dim MyFile As String
    MySql = ActiveSheet.QueryTables(1).Sql
    x = InStr(1, MySql, "FROM", vbTextCompare)
    MyFile = Right(MySql, Len(MySql) - x - 5)
    MsgBox (MyFile)

 

[BernieRS]

Thanks for the help, Brian, but I decided to have more control over the file selection by using:

f = Application.GetOpenFilename(FileFilter:="Text Files (*.txt) , *.txt",Title:="Open text file to import the data")
If f <> False Then
With ActiveSheet.QueryTables("Data")
.Connection = "TEXT;" & f
.TextFileStartRow = 3
.Refresh BackgroundQuery:=False
End With
Range("A34").Value = f

Instead of activating the wizard produced query via:

Range("B4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False