MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro import of BIG text file

Posted by JAF on April 10, 2001 1:24 AM


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.....


Posted by Dave Hawley on April 10, 2001 1:38 AM


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

The limit for Columns would then be 256

OzGrid Business Applications