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.