Recording a macro to open a large fixed-width text file
 

On a monthly basis i am having to open a fixed width text file which
is nearly a thousand columns, as you can imagine this takes some time.
Elsewhere in the groups i found the suggestion to record a macro to
open the file, however when i reached the end i got the message "Too
many line continuations".

I am not a visual basic whizz, i'm limited to recording and running
macros, is there any way i can record this macro without getting this
message? any help would be appreciated.

Recording a macro to open a large fixed-width text file
 

Try using the Macro Recorder:
Open a fresh New workbook
Save it as TestOpenTextFile.xls
Goto Tools, Macro, Record Macro
Ok out of Dialogbox,
Goto File Open, point to your text file, and OK;
Import Wizard should come up;
Step through settings answering carefully as you go,
After Clicking "Finish"
Stop Macro Recorder

Then see what you have...
Should resemble (Sample only):

Sub Macro1()
Workbooks.OpenText Filename:= _
"C:\My Documents\ABC.txt", Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
9), _
Array(13, 2), Array(23, 1), Array(33, 5), Array(41, 5), Array(49,
2), Array(50, 2)), _
TrailingMinusNumbers:=True
End Sub

Hope this helps,
Jim May


"Lucie Harris" wrote in message
om...
On a monthly basis i am having to open a fixed width text file which
is nearly a thousand columns, as you can imagine this takes some time.
Elsewhere in the groups i found the suggestion to record a macro to
open the file, however when i reached the end i got the message "Too
many line continuations".

I am not a visual basic whizz, i'm limited to recording and running
macros, is there any way i can record this macro without getting this
message? any help would be appreciated.

Recording a macro to open a large fixed-width text file
 

"thousand columns" - excel only has 256 columns. could you elaborate?

--
Regards,
Tom Ogilvy

"Lucie Harris" wrote in message
om...
On a monthly basis i am having to open a fixed width text file which
is nearly a thousand columns, as you can imagine this takes some time.
Elsewhere in the groups i found the suggestion to record a macro to
open the file, however when i reached the end i got the message "Too
many line continuations".

I am not a visual basic whizz, i'm limited to recording and running
macros, is there any way i can record this macro without getting this
message? any help would be appreciated.

Recording a macro to open a large fixed-width text file
 

Lucie:
I missed the "thousand columns" phrase you gave, until I read Tom Ogilvy's
response... Better forget my suggestion..
Sorry,

"Jim May" wrote in message
news:EOZYc.25794$wu.17947@okepread04...
Try using the Macro Recorder:
Open a fresh New workbook
Save it as TestOpenTextFile.xls
Goto Tools, Macro, Record Macro
Ok out of Dialogbox,
Goto File Open, point to your text file, and OK;
Import Wizard should come up;
Step through settings answering carefully as you go,
After Clicking "Finish"
Stop Macro Recorder

Then see what you have...
Should resemble (Sample only):

Sub Macro1()
Workbooks.OpenText Filename:= _
"C:\My Documents\ABC.txt", Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0,
9), _
Array(13, 2), Array(23, 1), Array(33, 5), Array(41, 5), Array(49,
2), Array(50, 2)), _
TrailingMinusNumbers:=True
End Sub

Hope this helps,
Jim May


"Lucie Harris" wrote in message
om...
On a monthly basis i am having to open a fixed width text file which
is nearly a thousand columns, as you can imagine this takes some time.
Elsewhere in the groups i found the suggestion to record a macro to
open the file, however when i reached the end i got the message "Too
many line continuations".

I am not a visual basic whizz, i'm limited to recording and running
macros, is there any way i can record this macro without getting this
message? any help would be appreciated.