I am working in Excel 2000 and need to program a way to open a text file
saved as a .dat file. I used the macro recorded and this is what I get.
When I use this it works, but I don't want to hardcode the file name. I have
a screen that the user can use to select the file and I store the path in a
variable. However, when I try to put the variable in the place of the actual
file name I get errors. How can I use a variable in the below:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\YI016695\My Documents\J&J\SDR-000J.DAT",
Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
1), Array(8, 1), Array(17, 1), Array(52, 1), Array(65, 1), Array(71,
1))

All you need to do is:

Dim Location As String

Location = "C:\Documents and Settings\YI016695\My Documents\J&J\SDR-000J.DAT"

Then the following code should work:

Workbooks.OpenText Filename:= _
Location,
Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
1), Array(8, 1), Array(17, 1), Array(52, 1), Array(65, 1), Array(71,
1))


"Dwaine Horton" wrote:

I am working in Excel 2000 and need to program a way to open a text file
saved as a .dat file. I used the macro recorded and this is what I get.
When I use this it works, but I don't want to hardcode the file name. I have
a screen that the user can use to select the file and I store the path in a
variable. However, when I try to put the variable in the place of the actual
file name I get errors. How can I use a variable in the below:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\YI016695\My Documents\J&J\SDR-000J.DAT",
Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
1), Array(8, 1), Array(17, 1), Array(52, 1), Array(65, 1), Array(71,
1))

It would be better to use the GetOpenFileName method, which allows you to
navigate to the file. Look it up in help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Shinkins" wrote in message
...
All you need to do is:

Dim Location As String

Location = "C:\Documents and Settings\YI016695\My
Documents\J&J\SDR-000J.DAT"

Then the following code should work:

Workbooks.OpenText Filename:= _
Location,
Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
1), Array(8, 1), Array(17, 1), Array(52, 1), Array(65, 1),
Array(71,
1))


"Dwaine Horton" wrote:

I am working in Excel 2000 and need to program a way to open a text file
saved as a .dat file. I used the macro recorded and this is what I get.
When I use this it works, but I don't want to hardcode the file name. I
have
a screen that the user can use to select the file and I store the path
in a
variable. However, when I try to put the variable in the place of the
actual
file name I get errors. How can I use a variable in the below:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\YI016695\My
Documents\J&J\SDR-000J.DAT",
Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
1), Array(8, 1), Array(17, 1), Array(52, 1), Array(65, 1),
Array(71,
1))