I have the following macro that works perfect for opening
a txt file.

Workbooks.OpenText Filename:= _
"C:\Path\Report200407JC.txt" _
, Origin:=xlWindows, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(5, 1), Array(10, 1),
Array(15, 1), Array(20, 1), Array(25, 1))

What I want to do is change the month in the name each
month. So I am using;

Dim Mth As String

Mth = "" & Format(DateSerial(Year(Date), Month(Date)
- 1, 1), "yyyymm")

Workbooks.OpenText Filename:= _
"C:\Path\Report & "Mth" & JC.txt" _
, Origin:=xlWindows, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(5, 1), Array(10, 1),
Array(15, 1), Array(20, 1), Array(25, 1))

I would like "Mth" to generate 200407 in Aug so the file
name generated would be Report200407JC.txt

But this always gives me a syntax error. Any ideas where
the error is

Hi Jack

"C:\Path\Report & "Mth" & JC.txt" _
should be
"C:\Path\Report" & Mth & "JC.txt" _

cheers
JulieD

"Jack S." wrote in message
...
I have the following macro that works perfect for opening
a txt file.

Workbooks.OpenText Filename:= _
"C:\Path\Report200407JC.txt" _
, Origin:=xlWindows, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(5, 1), Array(10, 1),
Array(15, 1), Array(20, 1), Array(25, 1))

What I want to do is change the month in the name each
month. So I am using;

Dim Mth As String

Mth = "" & Format(DateSerial(Year(Date), Month(Date)
- 1, 1), "yyyymm")

Workbooks.OpenText Filename:= _
"C:\Path\Report & "Mth" & JC.txt" _
, Origin:=xlWindows, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(5, 1), Array(10, 1),
Array(15, 1), Array(20, 1), Array(25, 1))

I would like "Mth" to generate 200407 in Aug so the file
name generated would be Report200407JC.txt

But this always gives me a syntax error. Any ideas where
the error is