Importing large file

[Brian Matlack]

Hi!
I sometimes need to import a data file that is over
one-hundred-thousand lines long. Usually I break-up the report into
several smaller reports and then consolidate them into several sheets
of an excel workbook.
Is there a way to do this in VBA that could do the entire file (100,000
lines or more) at once?

Any help or suggestions would be greatly appriciated! THANKS!!

 

[Guest]

Difficult to answer without knowing details of your source file:
1. Does it have column headers? (Or can it have column headers?)
2. Are the columns delimited or fixed width?

If the answers are Yes on both counts, you can use ADO to read the file &
CopyFromRecordset to send 65536 rows (or fewer) at a time to sheets, adding
sheets as you go along.

If the answers are not Yes on one or more counts, you can read the source
file one line at a time and send to Excel; if you keep a count of the number
of lines sent, you can add sheets as you go along.