Regular data file imports

N

nzcam

We have a General Ledger file that is beining imported by Users on
dialy basis into excel from a text file.

The column widths are identical every day.

Currently the Import wizard is used on a daily basis to split the dat
into the selected columns.

How can I have the file automatically complete the format when the fil
is opened or after the file is opened.
The file will have a different name each day.

Is there an easy way or will a macro have to be written.

Thanks

ca
 
J

Jim May

I receive a 4,000+ line bank statement each month in text format and have
recently automated the import of it into a 5 sheet setup; It is great. Use
the macro recorder as I did (turn it on) and Step through the File, Open
(target your *.txt file) and the Import Wizard should take over - carefully
(take your time) set everything up as you want it, when finished Stop
Macro - You are 90% of the way at this point -- the main single line of code
the recorder will create will look something like this (I'm using Excel
XP)..

Workbooks.OpenText Filename:= _
"C:\My Documents\MyCash\MyBank.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

You can customize the Code (with the help of this group). Good Luck,
Jim May
 
Top