MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Splitting long files

Posted by Dan Weil on October 29, 2001 12:29 AM

Dear all,
While loadins data file from external data logger I got an error message that the file was not loaded completely. Can I split such a file from within the Excel?

Posted by Damon Ostrander on October 29, 2001 7:38 PM

Hi Dan,

I assume the reason you are getting the message is that the file has more than 65536 (2^16) records, the maximum number of rows on a worksheet. I know of no way to automatically switch worksheets when the first gets full when you read in a text file by opening it in Excel. However, you can easily get around this limitation when you read the data into worksheets using standard I/O statements in Visual Basic for Applications (VBA). See the VBA help for reading data from files--the Open statement, Line Input # statement, EOF, Close, etc. This is also a very efficient way to read the data since you can also control what parts of each record to read into Excel and which parts not to.

I hope this helps.