I am not trying to rain on your parade but an excel file with 200,000+
records can be very problematic to deal with. Is it possible to load the text
file to a database such as Access instead. Access is much better suited to
deal with files of that size. If you want to use Excel as the front end that
is still easy to do via "Get External Data" queries or pivot tables linked
directly to the Access source. I have worked with files of that size and they
are nothing but trouble in Excel. Just my 2 cents...

Jim Thomlinson


"Scott Calkins via OfficeKB.com" wrote:

I am trying to use the macro that many people linked to here for importing
data longer than the 65536 rows permited in excel. Wen I run it, it loads
in the first line ok then errors out. It gives me a "Input past end of
file" error at Line Input #FileNum, ResultStr<. The full macro is as
follows:

Sub LargeFileImport()

Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
FileName = InputBox("Please enter the Text File's name, e.g.
test.txt")
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 = 65536 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop
Close
Application.StatusBar = False

End Sub


Due to I am loading in data that 1s 16 columns by 200000+ rows I would like
to get this working.

--
Message posted via http://www.officekb.com