As I was going along, after writing the below, I realized
that you Dim'd the Variables in both Sub's, or I needed to
because I had two subs. in the end that was the
Solution. I needed to declare the Dim's in both Sub's.

Again Thank you.

I see that you made another change,

I moved the Dim's to the sImportData Sub and it worked
like a champ. Is it possible to pass or make available
the variable to both Sub's? As you saw I have two Sub's
or should I just have one?

I intended sAddSheet to get the strFileName variable(file
name from the designated directory), and then create the
sheet with that name. The sImportData would also use this
variable to pull data from the file into the new sheet.

Your help is greatly appreciated

Rob





-----Original Message-----
This worked for me, going to a network drive:
I am using xl2000, so I had to comment out two lines that
were specific to
your later version, but beyond that it worked.

Try going to the immediate window and test your path to
your file

? dir("\\TestDIR01\OUTPUT\Test.txt")

If you get an error, then your path isn't correct or the
file doesn't exist.





Declarations moved to the top.

Dim strSheetNewName As String
Dim strFileName As String


Sub sImportData()
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\LOGFS06\users\ogilvtw\" & 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




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

--
Regards,
Tom Ogilvy


"
wrote in message
...
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



.