Morning all,
I found this macro here: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q190032&id=190032&SD=MSKB>Q190032
Now I am using Excel 2003 and it always fails at the BLUE text. Can someone tell me why and how to over come it?
I also tried replacing the ("TEXT") with ("F:\path\filename.txt") and still it fails. I need a code like this as the txt file I have is just short of 140,000 lines long.
Thanks in advance.
' 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
Bookmarks