Importing text files with varying names

[johenry]

Hello - I am trying to create a macro that allows me to import a text file via the import wizard. However, when I record the macro, I don't know how to modify the code so that the macro prompts me for the file name before importing. This would allow me to use this macro to open different file names. (the current file name is "PODetail_Orthopedic.txt")

Sample of recorded macro code:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\johenry\Documents\Ascension\Client Data\Spend\PO Data\PODetail_ORTHOPEDIC.txt" _
, Destination:=Range("$B$7"))
.Name = "PODetail_ORTHOPEDIC"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(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

 

[jmthompson]

Will the files to import always have the same names?

 

[mae0429]

Something like this:

    Dim fileLookup As String
    Dim fileToOpen As String
    fileLookup = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    fileToOpen = "TEXT;" & fileLookup
    With ActiveSheet.QueryTables.Add(Connection:=fileToOpen, Destination:=Range("A1"))

And then all your .Name, etc follows in the With block

 

[johenry]

Yes - the file names will change.

Thanks! That does work. One question, however. I still have the bit of code that reads:

.Name = "PODetail_ORTHOPEDIC"

in the code - will this cause any unintended affects as it is no longer the name of the file? At first blush it did not seem to, but I thought I would ask.