Try this but first check how many rows you actually have this code is set to 65536 alter it if need be.
You need to add the code to a module in your workbook.
If you run the code you will be shown an Input box which will allow you to select the file you want.
It imports your text and if it reaches the row 65536 it creates a new worksheet.
Hope it helps
Kev
PS it's not my code but I can't remember the author
Sub ImportBigText()
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 Error Then End
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
End Sub
This message was edited by swaink on 2002-04-18 05:18