Splitting a Text File and importing into multiple excel files

**DhavokD** · 07-27-2009, 06:19 PM

We have a giant text file that is produced weekly by a computer. This text file contains all of our financial information up to that point in time.

This text file is ~110,000 lines long, and gets bigger with more contracts, etc. So it isnt possible to just import all of the data at once and then work with it due to the issue of excel capping around 62k lines (addressing I guess).

The text file is currently turned into 13 separate reports, by hand, on a weekly basis (not all of the file is used). The person that normally does this finds the start of the pertinant data by a label. The label is a single line in the text file that has a name, which becomes the name of the excel file. There would also be something to mark the end of the report, but I still need to figure those out on an individual basis.

So what I need to do in VBA is:
1. Open a text file and read it line by line
2. Create new excel files and write to them

**Phil_V** · 07-27-2009, 07:37 PM

This code snippet should get you started with regards to Opening the text file, and searching for your 'starting' marker:

Please Login or Register  to view this content.

**DhavokD** · 07-28-2009, 10:21 AM

What is the Variable Type for "wholefile"?

String Array?

**Phil_V** · 07-28-2009, 10:37 AM

Oops, sorry, it's because in my code I have it declared outside of the sub.
For you the following should work:

Please Login or Register  to view this content.

**DhavokD** · 07-28-2009, 12:18 PM

Having issues opening the file....

Open ThisWorkbook.Path & "\" & "13JULY2009" For Binary As #1
ReDim filebytes(LOF(1) - 1)
Get #1, , filebytes
Close

The first line gives a "Subscript out of range" error.

FIXT: .txt helps!