XL: Importing Text Files Larger Than 16384 Rows

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

In versions of Microsoft Excel that are earlier than Microsoft Excel 97, text files that contain more than 16,384 rows cannot be opened in their entirety. You cannot open these files because earlier versions of Microsoft Excel are limited to 16,384 rows. If you open a file that contains more data than this, the following error message appears

   File not loaded completely

NOTE: Microsoft Excel 97 has a limit of 65,536 rows.

MORE INFORMATION

The following sample macro prompts you for a text filename, then opens the file into memory. If the number of rows is larger than the Microsoft Excel worksheet limit of 16,384, the macro breaks the file into multiple worksheets. This macro applies only to files you saved as text files and does not apply to any other file formats. The macro will not work with database file formats.

Note that because this is a macro, using it may be significantly slower than clicking Open on the File menu.

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/

   'All lines that begin with an apostrophe (') are remarks and are not
   'required for the macro to run.
   
   Sub LargeFileImport()
   
      'Dimension Variables
      Dim ResultStr As String
      Dim FileName As String
      Dim FileNum As Integer
      Dim Counter As Double
      'Ask User for File's Name
      FileName = InputBox("Please enter the Text File's name")
      'Check for no entry
      If FileName = "" Then End
      'Get Next Available File Handle Number
      FileNum = FreeFile()
      'Open Text File For Input
      Open FileName For Input As #FileNum
      'Turn Screen Updating Off
      Application.ScreenUpdating = False
      'Create A New WorkBook With One Worksheet In It
      Workbooks.Add template:=xlWorksheet
      'Set The Counter to 1
      Counter = 1
      'Loop Until the End Of File Is Reached
      Do While Seek(FileNum) <= LOF(FileNum)
         'Display Importing Row Number On Status Bar
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & FileName
          'Store One Line Of Text From File To Variable
          Line Input #FileNum, ResultStr
          'Store Variable Data Into Active Cell
          If Left(ResultStr, 1) = "=" Then
             ActiveCell.Value = "'" & ResultStr
          Else
             ActiveCell.Value = ResultStr
          End If
          If ActiveCell.Row = 16384 Then
             'If On The Last Row Then Add A New Sheet
             ActiveWorkbook.Sheets.Add
          Else
             'If Not The Last Row Then Go One Cell Down
             ActiveCell.Offset(1, 0).Select
          End If
          'Increment the Counter By 1
          Counter = Counter + 1
      'Start Again At Top Of 'Do While' Statement
      Loop
      'Close The Open Text File
      Close
      'Remove Message From Status Bar
      Application.StatusBar = False
   
   End Sub

When you run this macro on a Macintosh, and you are attempting to open a file that is on the desktop, the file name must be preceded by the following

   <Hard disk>:Desktop Folder: