The text import file has been modified to split large files into one or more ranges.
The maximum number of rows for each range is specified, together with a sufficient number of valid range names, which may be on the same sheet or different sheets. The modified file has been tested with a text file with about 1.4 million lines (43 MB). In Excel 2007 this is imported into two ranges in about 10 seconds, or about 75 seconds if it is split into 7 columns. In Excel 2000 the import stopped with a memory error after about 800,000 lines, so for very large files Excel 2007 does seem to have a clear advantage. On the down side, clearing large ranges in Excel 2007 is often excrutiatingly slow. Clearing the 1.4 million lines of data imported in 10 seconds took several minutes!
The ReadText UDF has also been modified to work with large files. It is now possible to specify a column with a list of line numbers, and only the specified lines will be imported. In this way the complete file may be imported into separate ranges, or parts of the file may be conveniently extracted. Note that if the UDF is used with a file exceeding the number of available lines in the spreadsheet the UDF will return an error if the line numbers have not been specified. For smaller files the line number list is optional.
The final refinement in this version is that the SplitText routines now allow the maximum number of columns to be specified. This is illustrated with a directory listing, with the file date and size details split into columns, but the name kept in one column, even if it includes spaces.
Bottom of range 1 (just to prove it’s all there!)
1.4 million line text file split into columns (about 75 seconds)
Reading a directory listing into 5 columns