The information in this article applies to:
- Microsoft Excel for Windows, versions 3.x, 4.x, 5.0 5.0c
- Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
- Microsoft Excel for Windows 95, version 7.0
SUMMARY
Text files that contain more than 16,384 rows cannot be opened in their
entirety in Microsoft Excel. You cannot open these files because a
worksheet is limited to 16,384 rows. If you open a file that contains more
data than this, the size will produce the following error message
File not loaded completely
and the text file will be truncated at the 16,384th row. However, you can
use a macro to open the file and automatically break the text into multiple
worksheets.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied, including, but not limited to, the
implied warranties of merchantability and/or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language being demonstrated and the tools used to create and debug
procedures. Microsoft support engineers can help explain the functionality
of a particular procedure, but they will not modify these examples to
provide added functionality or construct procedures to meet your specific
needs. If you have limited programming experience, you may want to contact
the Microsoft fee-based consulting line at (800) 936-5200. For more
information about the support options available from Microsoft, please see
the following page on the World Wide Web:
http://www.microsoft.com/supportnet/refguide/
The following sample macro prompts the user for a text file name, opens the
file into memory, and determines if the number of rows is larger than
16,384. If the number of rows is greater than the Microsoft Excel worksheet
limit, the macro separates the file into multiple worksheets.
To use this macro, follow these steps:
- Create a macro sheet.
Microsoft Excel Version 5.0 or 7.0
----------------------------------
Point to Macro on the Insert menu, and then click "MS Excel 4.0 Macro."
Microsoft Excel Version 3.0 or 4.0
----------------------------------
a. On the File menu, click New.
b. Select macro sheet in the New box, and then click OK.
- Enter following macro into the macro sheet:
A1: Large_Text_File_Importer
A2: filename=INPUT("Please enter the Text File's name",2,"Large Text
File Importer")
A3: =IF(filename=FALSE,RETURN())
A4: filenum=FOPEN(filename,2)
A5: =ECHO(FALSE)
A6: =NEW(1)
A7: Counter=1
A8: =WHILE(FPOS(filenum)<=FSIZE(filenum))
A9: =MESSAGE(TRUE,"Importing Row "&Counter&" of text file
"&filename)
A10: =FORMULA(FREADLN(filenum))
A11: =IF(ROW(ACTIVE.CELL())=16384)
A12: =NEW(1)
A13: =ELSE()
A14: =SELECT("R[1]C")
A15: =END.IF()
A16: Counter=Counter+1
A17: =NEXT()
A18: =FCLOSE(filenum)
A19: =MESSAGE(FALSE)
A20: =RETURN()
Description of macro code:
A1: Name of the Macro
A2: Prompt user for the Text file's name (with complete path and
file name extension).
A3: If input box cancel button was selected, then end macro.
A4: Open the Text file with Read access.
A5: Turn screen updating off.
A6: Create a new worksheet
A7: Initialize the row Counter name to the value of 1.
A8: Loop until we reach the end of the file.
A9: Display a status message at the bottom of Excel.
A10: Read a line from the text file and place into the active cell.
A11: Check to see if the current row is 16384.
A12: If A12 is True, then create a new sheet.
A13: If A12 is NOT True, then do the following...
A14: Select 1 row down from the active cell.
A15: End the IF block.
A16: Increment the Counter name by 1.
A17: Loop back to A9.
A18: Close the text file (opened in cell A4).
A19: Turn the status bar back to normal.
A20: End Macro.
- Next, define a name for the macro.
Microsoft Excel Version 5.0 or 7.0
----------------------------------
a. Point to Name on the Insert menu, and then click Define.
b. Type "ImportFile" (without the quotation marks) in the "Names in
Workbook" box.
c. Click Command in the Macro box, and then click OK
Microsoft Excel Version 3.0 or 4.0
----------------------------------
a. On the Formula Menu, click Define Name.
b. Type "ImportFile" (without the quotation marks) in the Name box.
c. Click Command in the Macro group box, and then click OK
- Run the macro.
Microsoft Excel Version 5.0 or 7.0
----------------------------------
a. On the Tools menu, click Macro.
b. Click ImportFile in the "Macro Name/Reference" box, and then
click OK.
Microsoft Excel Version 3.0 or 4.0
----------------------------------
a. On the Macro menu, click Run.
b. Click ImportFile in the Run box, and then click OK.
The text file that you import is broken up into multiple worksheets.
NOTE: This macro does not parse the data into columns. After you use this
macro, you may also need to do the following:
- If you are using Microsoft Excel 5.0, use the Text To Columns command on
the Data menu.
-or-
- If you are using Microsoft Excel 3.0, 4.0, or 4.0a, use the Data Parse
command or the Smart Parse command FLATFILE.XLA add-in macro.
Note also that, because this is a macro, it may be significantly slower
than clicking Open on the File menu.
|