Macro import of BIG text file
Posted by JAF on April 10, 2001 1:24 AM
Hi
I have a number of mainframe extract reports which are produced as comma seperated text files on a monthly basis.
Several of the reports are quite large (in excess of 100 columns of data). I believe that there is a limit on the number of columns that can be imported when using VBA code, but I don't know what the limitation is or how to get round it.
I've tried Microsoft's "Knowledge Base", but can't find anything to help.
Does anyone (and I'm thinking this is a "Dave" problem!!) have any suggestions as to how I can get a macro to open a comma delimited text file that contains over 100 columns of data. I don't need to do anything fancy to the data, I need all the columns and they all need to be imported as General format.
Over to you folks.....
JAF
Posted by Dave Hawley on April 10, 2001 1:38 AM
Hi JAF
You could have a macro that opens the file and then copies the 100 + columns to the Sheet of the workbook. If any numbers come in as text you could use some simple code like:
With Sheets("ImportSheet")
.Cells(1, 256) = 1
.Cells(1, 256).Copy
.UsedRange.PasteSpecial Operation:=xlPasteSpecialOperationMultiply
End With
Application.cutcopymode=False
The limit for Columns would then be 256
Dave
OzGrid Business Applications
