Export a text file which more than 65536 rows

[Navin]

guys i wanna export a text file which more than 65536 rows
into excel
excel has limation of transferring data to excel to 65536
now how do i handle rows more than 65536 if wanna export it to excel
i need to create extra spreadheets and export into it.
so do i go abt it...
i wanna do it way the fastest.
can you show me macro where by i can export all my data into excel and
create new sheets on the fly.

 

[Jed]

Macro to open large files greater than 65,536 lines.

' 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 Variant
Dim FileNum As Integer
Dim Counter As Double

' Ask User for file's name.
FileName = Application.GetOpenFilename("TEXT")

' Check for no entry.
If FileName = False 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

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 = 65536 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

Hope this helps.
Regards
Vinod.