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.
