Variant File Path for Inserting Text File

[julia55]

I am trying to figure out how I can make my file path a variant in the below code. I need to insert multiple text files into my current workbook and current cell selection, but the file path and file name will be different everytime. Please help!

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\kuiperj\My Documents\Projects\Standards Review\2011 Mid Year\STEP1 - RAW DATA\ALM1.RPT" _
        , Destination:=Range("$A$1"))
        .Name = "ALM1_4"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(20, 11, 14, 14, 14, 14, 14, 14)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

 

[taurean]

How will you get to know the Name as in Connection. If you want to pick up file everytime by navigating then:

Dim sName As String
sName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
sName = "TEXT;" & sName
With ActiveSheet.QueryTables.Add(Connection:=sName, Destination:=Range("$A$1"))
        .Name = "ALM1_4"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(20, 11, 14, 14, 14, 14, 14, 14)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
End With

 

[julia55]

Worked perfectly. Thanks!