I am trying to place a variable in the VBA Sub below. It
does not seem to work. My objective is to go to a
directory to retrieve a text file and import it as a Data
Query. The File name changes so I expect the need for a
variable in the place of the file name. This Sub is
called from another Sub. Both are included. The area in
question is noted with the - on either side


Sub sImportData()
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;-----\\TestDIR01\OUTPUT\" & strFileName-----
_
, Destination:=Range("A1"))
.Name = strFileName
.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 = 70
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ":"
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


Dim strSheetNewName As String
Dim strFileName As String


Sub sAddSheet()
strSheetNewName = "Test"
strFileName = "Test.txt"
Sheets.Add
ActiveSheet.Name = strSheetNewName
MsgBox "New Sheet " & strSheetNewName & "
added."
Call sImportData
End Sub