XL: Importing Text Files Larger Than 16384 Rows

ID: Q120596

The information in this article applies to:

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
and the text file is truncated at the row 16,384. However, you can use a macro to open the file and automatically break the text into multiple worksheets.

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 professionals 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/support/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
          
          'For xl97 and later change 16384 to 65536
          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


NOTE: The macro does not parse the data into columns. After using the macro, you may also need to use the Text To Columns command on the Data menu to parse the data as needed.



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:

where <Hard disk> is the name of your hard disk. Note that there is a space between the words Desktop and Folder.

Additional query words: 97 8.00 5.00a 5.00c import ascii xl97 xl7 xl5 


Keywords          : kbcode xlvbahowto 
Version           : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform          : MACINTOSH WINDOWS winnt 
Issue type        :

Last Reviewed: May 17, 1999