Extract Data From Text File Using VBA

[rcasaletta]

I am stuck and looking for assistant. I am importing only certain data from a text file, but because the number of lines changes with the dealers, I am getting overlapping data (incorrect). The code I am using has a 'For i = 1 to 30' statement and this is causing (I think) my problem. I would like to replace it, or a section of code, to stop when the word 'TOTAL' appears and then go to the next dealer. Below is my current code, and thank you in advance.

Option Base 1
Dim TextLine As String
Dim FileToOpen, A(11) As Variant
Dim c, R As Long
Private Sub CommandButton1_Click()
Dim xLast_Row As Long, xLast_Col As Long, I As Integer


Reset 'Close any open text files
  
  ' Display the File*Open dialog box
    FileToOpen = Application.GetOpenFilename( _
        fileFilter:="Text Files (*.txt), *.txt", _
        Title:="Open the FCAN6245-R1 DEALER MODEL LINE ANALYSIS text file")
    If FileToOpen = False Then End
    
    xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
    xLast_Col = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Column
    If xLast_Row = 1 Then xLast_Row = 2
    
    Range("A2:K55000").ClearContents 'Clear old data
    Open FileToOpen For Input As #1 'Open the text file
    
    'Set-up row count.
    R = 1
    
    Do While Not EOF(1)
        Line Input #1, TextLine 'Get next line from text file
        
        'Skip until Page 2.
        If InStr(1, TextLine, "REPORT NO. FCAN6245-R1", vbTextCompare) > 0 Then
            
            'Get Dealer info...
            Line Input #1, TextLine
           ' Line Input #1, TextLine
                A(1) = Mid(TextLine, 11, 14) 'Region
                A(2) = Mid(TextLine, 32, 2) 'District
'            Line Input #1, TextLine
            Line Input #1, TextLine
                A(3) = Trim(Mid(TextLine, 8, 5)) 'Dealer Code
                A(4) = Mid(TextLine, 15, 25) 'Dealer Name
            Line Input #1, TextLine
            Line Input #1, TextLine
            Line Input #1, TextLine
'            Line Input #1, TextLine
            Line Input #1, TextLine
            For I = 1 To 30
                A(5) = Mid(TextLine, 1, 3) 'Model Line
                A(6) = Trim(Mid(TextLine, 8, 4)) 'Model Line VINS
                A(7) = Trim(Mid(TextLine, 16, 4)) 'Model Line Claims
                A(8) = Mid(TextLine, 24, 6) 'Model Line Labor Hours
                A(9) = Trim(Mid(TextLine, 32, 10)) 'Model Line Parts $
                A(10) = Mid(TextLine, 46, 6) 'Model Line Sublet/Freight $
                A(11) = Mid(TextLine, 58, 10) 'Model Line Total Warranty $
                Call Write_Row
                Line Input #1, TextLine
                R = R + 1
            Next
                
        End If
    Loop
    Close #1


    MsgBox "Finally, the records have been imported"
    
End Sub


Sub Write_Row()


Range("A1").Offset(R, 0).Value = A(1)
Range("A1").Offset(R, 1).Value = A(2)
Range("A1").Offset(R, 2).Value = A(3)
Range("A1").Offset(R, 3).Value = A(4)
Range("A1").Offset(R, 4).Value = A(5)
Range("A1").Offset(R, 5).Value = A(6)
Range("A1").Offset(R, 6).Value = A(7)
Range("A1").Offset(R, 7).Value = A(8)
Range("A1").Offset(R, 8).Value = A(9)
Range("A1").Offset(R, 9).Value = A(10)
Range("A1").Offset(R, 10).Value = A(11)




End Sub