Extract Data From Text File Using VBA
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.
Code:
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
