Import a text file into an excel spreadsheet using VBA

[mel_flynn]

Hi all,

I want to import the contents of a text file into an Excel Sheet. I am using the following VBA code: Path = Workbooks("NITBSum_Macro.xls").Worksheets("Network").Cells(4, 2).Value
Workbooks.OpenText FileName:=Path & "JT_Insurance_Report.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1))
Cells(1, 1).Select
Selection.EntireRow.Insert
This is working fine for most text files, however I have one file that has more than 100,000 lines, which exceeds the maximum allowed 65,000 lines in an excel document.

So my question is:
1. How can I get it to automatically create a second excel file with the remaining contents of the text file? At the moment it just returns an error when it reaches 65,000 lines.

Thanks,

[Hack]

Moved to Office Development

[Comintern]

You'll probably have to read and parse the file manually, so you can keep track of what line you are writing to. I dug up one of my import modules for a long csv report I use at work to give you a starting point:
Private Function TXReader() As Boolean

Dim sFileName As String, sTrans As String, sSheetName As String, iPage As Integer, iFile As Integer
Dim lLineNum As Long, oBook As Workbook, oSheet As Worksheet, sValues() As String, iCount As Integer

On Error GoTo ErrHand

sFileName = Application.GetOpenFilename("Report Files (*.prt),*.prt,All Files (*.*),*.*", 1, _
"Select TX Report to Open")
If sFileName <> "False" Then
iFile = FreeFile
lLineNum = 2
iPage = 1

Application.ScreenUpdating = False
Set oBook = Application.ActiveWorkbook
sSheetName = ParseName(sFileName) 'Custom function, strips the path.
Open sFileName For Input As #iFile
Set oSheet = Application.ActiveSheet
oSheet.Name = sSheetName

Do Until EOF(iFile)
Line Input #iFile, sTrans
'Replace with whatever you need to do to parse and write the line.
sValues = Split(sTrans, ",")
For iCount = 0 To UBound(sValues)
oSheet.Cells(lLineNum, iCount + 1) = sValues(iCount)
Next iCount
'End replace.
lLineNum = lLineNum + 1
If lLineNum > 65000 Then
Set oSheet = oBook.Worksheets.Add
iPage = iPage + 1
oSheet.Name = sSheetName & " Page " & CStr(iPage)
lLineNum = 2
End If
Loop

If iPage > 1 Then
Call MsgBox("Input file was too long -- Worksheets added.")
End If

TXReader = True
End If

ErrHand:
Application.ScreenUpdating = True
If Err.Number <> 0 Then
MsgBox "Error number " & Err.Number & ":" & vbCrLf & Err.Description & vbCrLf & "In module TXReader."
Err.Clear
End If

End Function

[mel_flynn]

Thanks, will give me a start anyway.