MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP! How do I import files with > 65K lines?


Posted by Bob Sleeper on March 12, 2001 1:18 PM

Any suggestions would be greatly appreciated...


Posted by Mark W. on March 12, 2001 1:24 PM

You can't import a file with more than 65,536
(1,024 x 64) records. You'll have to query this
data using Excel's External Data or PivotTable
features.

Posted by Bob on March 12, 2001 1:38 PM

GOTTA BE A WAY TO EDIT A VERY LARGE FIEL. CAN I CHANGE THE MEMORY ALLOCATION UPON START-UP?

Posted by Mark W. on March 12, 2001 1:44 PM

Nope! See the Excel Help Topic for
"What's new with specifications and performance?".

Posted by Rob on March 14, 2001 6:26 AM

Bob,

I use this to import text files with more than 65536 lines:

Sub LargeFileImport()
Dim ResultStr As String, FileName As String
Dim FileNum As Integer
Dim Counter As Double

FileName = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Please select the huge text file to import (more than 65536 lines)")

If FileName = "" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False
Workbooks.Add template:=xlWorksheet
Counter = 1
Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = "Importing Row " & Counter & " of text file " & FileName
Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
If ActiveCell.Row = 65530 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop
Close
Application.StatusBar = False
End Sub

Hope it helps

Rob

Posted by Mark W. on March 14, 2001 10:24 AM

Rob's right...

...if you don't mind breaking your data into separate
worksheets. In fact, here's Microsoft's
recommendation:

http://support.microsoft.com/support/kb/articles/Q120/5/96.asp?LN=EN-US&SD=gn&FR=0&qry=more%20than%2065536&rnk=6&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97