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

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

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



.

Dim strSheetNewName As String
Dim strFileName As String

declared outside any procedures at the top of the module make these visible
module wide - all procedures can see them unless you declare them again
inside a procedure - then these locally declared variables take precedence
and hide your module level variables. So the procedure with the local
declarations would not be able to see the values in the module level
variables. The way you had your subs written, you should only declare them
at module level. Otherwise, sImportData would not see what the assigned
values are for the two variables.

I am not sure why you say I dim'd the variables in both subs - I did not dim
them in either sub. I moved the declaration to the top of the module so
they would a) not raise an error b) be visible to both subs.

Having two subs is fine. Having one sub would be fine as well in this case.
The functionality of each is clearly different, so it is appropriate to have
them separate. If you wanted to write another interface sub that then
calls sImportData, then that would be an advantage of having sImportData as
a separate sub (you have two or more interface subs that both us sImportData
to do the import).

--
Regards,
Tom Ogilvy


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



.

Thank you for your help. You have helped in several ways.

Robert
-----Original Message-----
Dim strSheetNewName As String
Dim strFileName As String

declared outside any procedures at the top of the module
make these visible
module wide - all procedures can see them unless you
declare them again
inside a procedure - then these locally declared
variables take precedence
and hide your module level variables. So the procedure
with the local
declarations would not be able to see the values in the
module level
variables. The way you had your subs written, you should
only declare them
at module level. Otherwise, sImportData would not see
what the assigned
values are for the two variables.

I am not sure why you say I dim'd the variables in both
subs - I did not dim
them in either sub. I moved the declaration to the top
of the module so
they would a) not raise an error b) be visible to both
subs.

Having two subs is fine. Having one sub would be fine as
well in this case.
The functionality of each is clearly different, so it is
appropriate to have
them separate. If you wanted to write another interface
sub that then
calls sImportData, then that would be an advantage of
having sImportData as
a separate sub (you have two or more interface subs that
both us sImportData
to do the import).

--
Regards,
Tom Ogilvy


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



.



.