Extract .txt data on page break

[rcasaletta]

I am looking for help on how to import text file data based on the files page break. My current VBA is below and writes the data to Excel, but does not do it by page &/or dealer.

Any help is appreciated (text file example follows 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

Text File:
DATE: 05/06/2019
TIME: 08:19.45

DATE 05/05/19 OEM (OEM ) REPORT NO. FCAN6245-R1 PAGE 1454
REGION 88 WEST DIST 08 DEALER MODEL LINE ANALYSIS FROM PERIOD Nov18 THRU Apr19
DEALER 1111 AUTOS FOREVER
------------------------------ REGULAR WARRANTY 6-MONTH DATA ------------------------------ ------------ GOODWILL -----------
MODEL LABOR PARTS SUB/FRT TOTAL TOURIST NON-TOURIST % OF
LINE VINS CLAIMS HOURS DOLLARS DOLLARS DOLLARS ACPVS ACPVS VINS CLAIMS DOLLARS TOTAL
ALC 1 1 .2 141.27 .00 168.62 154.27 0.00 0 0 .00 0.00
ALT 259 315 508.4 69,237.78 588.08 139,354.70 447.95 357.90 51 53 9,286.71 6.25
ARM 18 18 22.0 1,860.69 .00 4,869.40 162.88 202.76 2 2 60.00 1.22
GTR 1 1 1.8 616.21 .00 862.38 733.21 0.00 0 0 .00 0.00
JKE 9 10 9.3 895.41 .00 2,167.26 66.33 195.32 2 2 60.00 2.69
KIX 23 29 45.8 14,525.80 218.00 21,007.43 929.97 736.89 8 9 670.03 3.09
LEF 16 17 10.8 1,355.16 .00 2,832.21 113.04 148.54 1 1 150.00 5.03
MAX 37 54 88.5 15,981.35 .00 28,084.65 729.74 452.56 16 17 2,012.52 6.69
MUR 57 76 119.4 21,858.92 .00 38,188.02 389.67 614.17 20 24 16,361.54 29.99
NV 16 18 37.2 6,638.94 .00 11,726.40 321.44 677.25 2 2 241.98 2.02
NVP 7 12 12.9 2,193.76 .00 3,957.96 967.75 344.09 3 3 340.33 7.92
NV2 12 16 41.5 5,205.97 492.34 11,373.84 353.54 872.71 3 3 528.66 4.44
PTH 86 99 175.6 19,756.86 .00 43,771.91 454.77 313.00 17 17 3,608.18 7.62
QST 9 9 20.2 1,059.23 451.31 4,273.11 301.25 323.71 3 4 1,318.19 23.58
RGE 169 195 252.3 47,183.19 136.76 81,824.34 368.36 379.91 37 38 6,284.83 7.13
RGS 6 8 24.4 773.40 .00 4,110.34 26.00 466.68 1 1 90.00 2.14
RSP 25 29 19.6 2,611.47 287.20 5,579.15 202.60 160.11 3 4 210.00 3.63
SEN 402 515 541.0 67,212.64 273.52 141,472.67 212.05 282.81 38 42 10,182.76 6.71
TTN 19 26 20.2 8,564.93 437.63 11,765.10 613.71 501.63 2 2 175.00 1.47
TXD 13 23 19.5 8,854.15 .00 11,520.98 926.55 605.97 5 6 405.00 3.40
UNK 22 23 14.0 6,139.35 .00 8,053.97 320.36 320.55 1 1 105.00 1.29
VER 27 31 31.6 4,627.91 .00 8,949.48 281.39 227.53 5 6 2,922.08 24.61
VSD 43 54 65.6 16,459.57 136.76 25,567.76 651.87 230.10 8 9 1,470.93 5.44
XTR 3 3 2.0 303.67 .00 577.19 225.53 104.07 0 0 .00 0.00
Z 11 13 20.0 3,297.13 232.49 6,264.82 152.18 546.64 1 2 360.00 5.43
720 41 50 62.0 10,459.88 164.11 19,103.13 287.59 393.62 3 3 369.36 1.90




TOTAL 1332 1,645 2165.8 337,814.64 3,418.20 637,426.82 369.20 357.32 232 251 57,213.10 8.24
REGULAR WARRANTY PER VIN
MODEL -------CLAIMS------- ------LABOR HOURS------ -----PARTS DOLLARS----- --------SUB/FRT-------- -----TOTAL DOLLARS----- -------
LINE DLR DST RGN DLR DST RGN DLR DST RGN DLR DST RGN DLR DST RGN % RGN
ALC 1.00 1.00 1.00 .20 2.90 .88 141.27 594.59 261.56 .00 .00 .00 154.27 783.09 319.05 48.35
ALT 1.22 1.43 1.33 1.96 2.74 2.03 267.33 296.12 290.95 2.27 .83 .90 397.19 474.97 423.85 93.71
ARM 1.00 1.17 1.37 1.22 2.25 2.14 103.37 409.78 369.25 .00 .00 4.15 182.82 555.99 512.24 35.69
GTR 1.00 1.56 1.92 1.80 4.40 4.98 616.21 1545.81 1385.57 .00 .00 7.67 733.21 1831.81 1716.97 42.70
JKE 1.11 1.22 1.26 1.03 2.13 1.94 99.49 218.64 398.60 .00 .00 3.00 166.66 356.92 527.77 31.58
KIX 1.26 1.32 1.36 1.99 1.87 1.56 631.56 387.25 525.51 9.48 12.68 17.71 770.47 521.60 644.70 119.51
LEF 1.06 1.25 1.19 .68 2.57 2.00 84.70 281.98 289.46 .00 .51 .81 128.57 449.52 420.16 30.60
MAX 1.46 1.46 1.40 2.39 3.09 2.38 431.93 302.72 324.01 .00 1.41 1.41 587.40 504.84 480.12 122.34
MUR 1.33 1.38 1.34 2.09 2.24 2.11 383.49 298.89 353.24 .00 2.03 1.74 519.65 446.72 492.33 105.55
NV 1.13 1.29 1.30 2.33 1.84 1.66 414.93 472.33 387.01 .00 .61 2.54 566.06 592.53 497.16 113.86
NVP 1.71 1.59 1.56 1.84 1.36 1.56 313.39 354.15 381.86 .00 5.43 4.38 433.18 448.22 487.61 88.84
NV2 1.33 1.45 1.40 3.46 1.90 1.67 433.83 374.50 387.39 41.03 3.02 1.78 699.65 501.26 497.45 140.65
PTH 1.15 1.37 1.34 2.04 3.04 2.39 229.73 268.03 307.54 .00 .36 1.51 362.45 465.97 464.62 78.01
QST 1.00 1.33 1.26 2.24 4.79 3.40 117.69 313.57 324.59 50.15 9.21 1.78 313.73 634.38 547.11 57.34
RGE 1.15 1.32 1.31 1.49 2.18 1.84 279.19 295.70 310.93 .81 1.11 2.52 377.04 438.52 433.22 87.03
RGS 1.33 1.19 1.19 4.07 3.07 2.06 128.90 263.70 284.11 .00 .00 .00 393.23 463.17 417.99 94.08
RSP 1.16 1.23 1.21 .78 1.09 1.12 104.46 165.85 199.78 11.49 1.42 1.36 166.91 237.87 273.99 60.92

DATE 05/05/19 OEM (OEM ) REPORT NO. FCAN6245-R1 PAGE 1454
REGION 88 WEST DIST 08 DEALER MODEL LINE ANALYSIS FROM PERIOD Nov18 THRU Apr19
DEALER 1111 AUTOS FOREVER
------------------------------ REGULAR WARRANTY 6-MONTH DATA ------------------------------ ------------ GOODWILL -----------
MODEL LABOR PARTS SUB/FRT TOTAL TOURIST NON-TOURIST % OF
LINE VINS CLAIMS HOURS DOLLARS DOLLARS DOLLARS ACPVS ACPVS VINS CLAIMS DOLLARS TOTAL
SEN 1.28 1.45 1.40 1.35 1.94 1.58 167.20 226.28 234.09 .68 .40 .81 255.35 352.84 337.31 75.70
TTN 1.37 1.44 1.50 1.06 2.64 2.51 450.79 482.48 524.93 23.03 6.96 7.86 542.92 660.78 696.23 77.98
TXD 1.77 1.85 1.67 1.50 2.87 2.22 681.09 759.05 551.08 .00 12.62 8.13 778.59 958.10 703.76 110.63
UNK 1.05 1.08 1.07 .64 1.47 1.02 279.06 266.33 297.19 .00 .00 .13 320.43 361.84 363.45 88.16
VER 1.15 1.31 1.24 1.17 2.17 1.78 171.40 230.61 257.19 .00 .00 .37 247.48 371.60 373.58 66.25
VSD 1.26 1.38 1.28 1.53 2.40 1.89 382.78 280.71 284.38 3.18 .59 .87 485.12 437.39 407.85 118.95
XTR 1.00 1.06 1.06 .67 1.76 1.78 101.22 414.76 452.31 .00 .00 .00 144.56 529.23 567.98 25.45
Z 1.18 1.38 1.30 1.82 2.46 1.96 299.74 567.53 478.73 21.14 12.83 6.60 439.06 740.04 612.82 71.65
720 1.22 1.40 1.33 1.51 2.06 1.70 255.12 391.60 324.70 4.00 3.01 3.08 357.41 528.66 438.11 81.58
TOTAL 1.23 1.40 1.34 1.63 2.30 1.85 253.61 288.01 296.62 2.57 1.39 1.83 361.87 438.81 418.88 86.39

DATE 05/05/19 OEM (OEM ) REPORT NO. FCAN6245-R1 PAGE 1454
REGION 88 WEST DIST 08 DEALER MODEL LINE ANALYSIS FROM PERIOD Nov18 THRU Apr19
DEALER 2222 FOREVER AUTOS
------------------------------ REGULAR WARRANTY 6-MONTH DATA ------------------------------ ------------ GOODWILL -----------
MODEL LABOR PARTS SUB/FRT TOTAL TOURIST NON-TOURIST % OF
LINE VINS CLAIMS HOURS DOLLARS DOLLARS DOLLARS ACPVS ACPVS VINS CLAIMS DOLLARS TOTAL
ALC 1 1 5.0 1,039.07 .00 1,669.07 1364.07 0.00 1 1 380.00 18.54
ALT 425 666 1197.4 143,411.36 .00 294,283.76 680.81 406.20 48 49 8,495.51 2.81
ARM 17 19 25.0 3,422.34 .00 6,572.34 384.59 136.15 3 3 319.91 4.64
CBE 1 1 7.2 66.99 .00 974.19 0.00 534.99 0 0 .00 0.00
GTR 3 8 30.2 11,339.20 .00 15,144.40 5884.93 1532.34 1 1 500.00 3.20
JKE 11 14 23.4 1,583.09 .00 4,531.49 312.43 229.27 1 1 33.00 0.72
KIX 29 36 22.5 2,158.76 .00 4,993.76 0.00 124.87 3 3 1,118.00 18.29
LEF 19 23 48.5 7,060.21 .00 13,171.21 787.51 448.23 3 3 3,508.00 21.03
MAX 49 68 185.8 7,683.90 .00 31,094.70 473.72 350.46 5 5 421.72 1.34
MUR 45 70 131.1 9,819.75 591.50 26,929.85 456.73 404.47 10 12 1,927.17 6.68
NV 18 22 33.7 11,518.06 .00 15,764.26 1285.95 237.22 4 4 1,708.46 9.78
NVP 2 4 4.5 430.48 .00 997.48 304.18 418.80 0 0 .00 0.00
NV2 26 35 59.9 3,638.11 .00 11,185.51 404.43 205.53 2 2 442.26 3.80
PTH 85 127 379.8 19,853.03 .00 67,707.83 500.70 537.38 14 18 1,610.53 2.32
QST 8 12 64.9 2,774.54 .00 10,951.94 911.70 611.14 7 7 774.95 6.61
RGE 269 367 626.1 63,476.61 .00 142,365.21 539.51 313.24 28 28 4,834.74 3.28
RGS 5 5 4.7 166.01 .00 758.21 78.10 98.35 1 1 252.00 24.95
RSP 59 66 41.1 864.29 .00 6,042.89 119.42 53.19 3 3 1,943.90 24.34
SEN 515 830 1285.0 146,170.07 450.17 308,530.24 414.47 466.80 62 72 7,464.52 2.36
TTN 8 13 18.5 8,987.71 .00 11,318.71 1029.67 1517.89 2 3 526.12 4.44
TXD 10 21 33.3 11,665.67 126.00 15,987.47 2250.88 540.35 3 3 2,151.99 11.86
UNK 34 35 57.2 6,516.91 .00 13,724.11 292.45 321.62 2 2 370.44 2.63
VER 38 54 111.4 10,200.79 .00 24,237.19 458.66 459.37 4 4 783.58 3.13
VSD 99 141 296.1 25,179.20 .00 62,487.80 472.40 438.46 20 21 3,039.39 4.64
XTR 5 5 16.1 4,389.83 .00 6,418.43 88.99 1336.84 2 2 76.00 1.17
Z 4 6 9.7 3,143.01 447.34 4,812.55 1225.36 885.07 3 4 1,370.26 22.16
ZRD 2 5 8.9 2,182.89 .00 3,304.29 2527.39 234.00 0 0 .00 0.00
720 105 146 233.8 35,524.28 151.20 65,131.62 631.18 414.26 10 11 2,508.58 3.71




TOTAL 1892 2,800 4960.8 544,266.16 1,766.21 1171,090.51 564.65 395.73 242 263 46,561.03 3.82
REGULAR WARRANTY PER VIN
MODEL -------CLAIMS------- ------LABOR HOURS------ -----PARTS DOLLARS----- --------SUB/FRT-------- -----TOTAL DOLLARS----- -------
LINE DLR DST RGN DLR DST RGN DLR DST RGN DLR DST RGN DLR DST RGN % RGN
ALC 1.00 1.00 1.00 5.00 2.90 .88 1039.07 594.59 261.56 .00 .00 .00 1364.07 783.09 319.05 427.54
ALT 1.57 1.43 1.33 2.82 2.74 2.03 337.44 296.12 290.95 .00 .83 .90 520.57 474.97 423.85 122.82
ARM 1.12 1.17 1.37 1.47 2.25 2.14 201.31 409.77 369.25 .00 .00 4.15 296.90 555.99 512.24 57.96
CBE 1.00 1.00 1.00 7.20 7.20 1.52 66.99 66.99 288.53 .00 .00 .00 534.99 534.99 387.25 138.15
GTR 2.67 1.56 1.92 10.07 4.40 4.98 3779.73 1545.81 1385.57 .00 .00 7.67 4434.07 1831.81 1716.97 258.25
JKE 1.27 1.22 1.26 2.13 2.13 1.94 143.92 218.64 398.60 .00 .00 3.00 282.19 356.92 527.77 53.47
KIX 1.24 1.32 1.36 .78 1.87 1.56 74.44 387.25 525.51 .00 12.68 17.71 124.87 521.60 644.70 19.37
LEF 1.21 1.25 1.19 2.55 2.57 2.00 371.59 281.98 289.46 .00 .51 .81 537.51 449.52 420.16 127.93
MAX 1.39 1.46 1.40 3.79 3.09 2.38 156.81 302.72 324.01 .00 1.41 1.41 403.28 504.84 480.12 84.00
MUR 1.56 1.38 1.34 2.91 2.24 2.11 218.22 298.89 353.24 13.14 2.03 1.74 420.73 446.72 492.33 85.46
NV 1.22 1.29 1.30 1.87 1.84 1.66 639.89 472.33 387.01 .00 .61 2.54 761.59 592.53 497.16 153.19
NVP 2.00 1.59 1.56 2.25 1.36 1.56 215.24 354.15 381.86 .00 5.43 4.38 361.49 448.22 487.61 74.14
NV2 1.35 1.45 1.40 2.30 1.90 1.67 139.93 374.50 387.39 .00 3.02 1.78 289.68 501.26 497.45 58.23
PTH 1.49 1.37 1.34 4.47 3.04 2.39 233.57 268.03 307.54 .00 .36 1.51 524.00 465.97 464.62 112.78
QST 1.50 1.33 1.26 8.11 4.79 3.40 346.82 313.57 324.59 .00 9.21 1.78 874.13 634.38 547.11 159.77

DATE 05/05/19 OEM (OEM ) REPORT NO. FCAN6245-R1 PAGE 1454
REGION 88 WEST DIST 08 DEALER MODEL LINE ANALYSIS FROM PERIOD Nov18 THRU Apr19
DEALER 2222 FOREVER AUTOS
------------------------------ REGULAR WARRANTY 6-MONTH DATA ------------------------------ ------------ GOODWILL -----------
MODEL LABOR PARTS SUB/FRT TOTAL TOURIST NON-TOURIST % OF
LINE VINS CLAIMS HOURS DOLLARS DOLLARS DOLLARS ACPVS ACPVS VINS CLAIMS DOLLARS TOTAL
RGE 1.36 1.32 1.31 2.33 2.18 1.84 235.97 295.70 310.93 .00 1.11 2.52 387.26 438.52 433.22 89.39
RGS 1.00 1.19 1.19 .94 3.07 2.06 33.20 263.70 284.10 .00 .00 .00 94.30 463.17 417.99 22.56
RSP 1.12 1.23 1.21 .70 1.09 1.12 14.65 165.85 199.78 .00 1.42 1.36 59.93 237.87 273.99 21.87
SEN 1.61 1.45 1.40 2.50 1.94 1.58 283.83 226.28 234.09 .87 .40 .81 446.88 352.84 337.31 132.48
TTN 1.63 1.44 1.50 2.31 2.64 2.51 1123.46 482.48 524.93 .00 6.96 7.86 1273.78 660.78 696.23 182.95
TXD 2.10 1.85 1.67 3.33 2.87 2.22 1166.57 759.05 551.08 12.60 12.62 8.13 1395.62 958.10 703.76 198.31
UNK 1.03 1.08 1.07 1.68 1.47 1.02 191.67 266.33 297.19 .00 .00 .13 301.03 361.84 363.45 82.83
VER 1.42 1.31 1.24 2.93 2.17 1.78 268.44 230.61 257.19 .00 .00 .37 458.99 371.60 373.58 122.86
VSD 1.42 1.38 1.28 2.99 2.40 1.89 254.34 280.71 284.38 .00 .59 .87 448.74 437.39 407.85 110.03
XTR 1.00 1.06 1.06 3.22 1.76 1.78 877.97 414.76 452.31 .00 .00 .00 1087.27 529.23 567.98 191.43
Z 1.50 1.38 1.31 2.43 2.46 1.96 785.75 567.53 478.74 111.84 12.83 6.60 1055.21 740.04 612.82 172.19
ZRD 2.50 1.80 1.23 4.45 2.38 1.74 1091.45 695.20 344.15 .00 .00 11.44 1380.70 849.90 468.71 294.57
720 1.39 1.40 1.33 2.23 2.06 1.70 338.33 391.60 324.70 1.44 3.01 3.08 484.50 528.66 438.11 110.59
TOTAL 1.48 1.39 1.34 2.62 2.28 1.83 287.67 285.79 291.85 .93 1.21 1.71 459.03 435.13 412.19 111.36

DATE 05/05/19 OEM (OEM ) REPORT NO. FCAN6245-R1 PAGE 1454
REGION 88 WEST DIST 08 DEALER MODEL LINE ANALYSIS FROM PERIOD Nov18 THRU Apr19
DEALER 3333 SOMEWHERE AUTOS
------------------------------ REGULAR WARRANTY 6-MONTH DATA ------------------------------ ------------ GOODWILL -----------
MODEL LABOR PARTS SUB/FRT TOTAL TOURIST NON-TOURIST % OF
LINE VINS CLAIMS HOURS DOLLARS DOLLARS DOLLARS ACPVS ACPVS VINS CLAIMS DOLLARS TOTAL
ALC 1 1 2.3 1,163.62 .00 1,435.02 1313.12 0.00 0 0 .00 0.00
ALT 250 364 519.7 60,317.30 129.80 122,120.00 372.97 379.91 39 42 4,172.50 3.30
ARM 6 9 9.3 1,023.17 .00 2,132.27 366.73 175.83 1 1 30.00 1.39
GTR 0 0 .0 .00 .00 .00 0.00 0.00 1 1 152.55 0.00
JKE 4 5 3.1 1,980.85 .00 2,355.65 378.81 712.37 2 2 416.10 15.01
KIX 11 12 27.4 2,365.71 .00 5,598.91 270.49 387.62 2 2 105.00 1.84
LEF 25 31 45.0 5,174.90 .00 10,490.30 250.34 373.10 4 4 390.00 3.58
MAX 36 47 78.5 6,623.78 236.00 16,124.58 238.71 379.07 9 10 777.46 4.60
MUR 37 48 66.1 8,558.32 47.20 16,425.12 434.40 328.71 8 9 2,059.16 11.14
NV 16 20 27.7 7,285.16 .00 10,553.76 374.41 683.92 2 2 1,352.67 11.36
NVP 8 11 9.8 2,491.53 .00 3,647.93 166.67 465.87 2 2 198.95 5.17
NV2 32 41 47.6 14,541.29 .00 20,170.69 663.09 364.47 5 5 806.77 3.85
PTH 78 102 114.9 15,614.74 .00 29,231.44 336.97 265.85 16 17 3,843.28 11.62
QST 8 11 42.9 3,617.94 .00 8,724.24 811.90 723.12 3 3 815.70 8.55
RGE 135 175 238.2 27,465.63 519.20 56,394.83 400.49 280.05 18 21 2,248.35 3.83
RGS 3 3 15.6 109.86 .00 1,950.66 374.62 0.00 0 0 .00 0.00
RSP 27 37 40.1 5,765.48 .00 10,542.28 521.55 140.89 3 3 325.00 2.99
SEN 465 647 737.9 84,036.00 118.00 171,675.30 289.25 281.89 36 43 4,905.03 2.78
TTN 23 31 50.3 18,235.28 118.00 24,294.98 868.89 965.26 4 4 290.00 1.18
TXD 3 4 4.9 1,233.47 200.60 2,012.27 318.05 717.26 1 1 70.00 3.36
UNK 28 29 28.7 5,048.30 .00 8,434.90 259.43 201.05 8 9 653.16 7.19
VER 23 32 36.7 3,594.03 .00 7,974.13 283.18 249.83 4 4 385.54 4.61
VSD 37 44 98.7 9,837.38 106.20 21,601.88 495.90 378.89 7 7 2,564.57 10.61
XTR 2 2 .8 176.37 .00 270.77 114.19 0.00 0 0 .00 0.00
Z 10 10 16.3 5,119.87 .00 7,043.27 747.54 423.54 0 0 .00 0.00
720 50 73 90.9 14,678.24 .00 25,415.24 258.97 577.24 2 3 600.40 2.31




TOTAL 1318 1,789 2353.4 306,058.22 1,475.00 586,620.42 369.01 337.21 177 195 27,162.19 4.43
REGULAR WARRANTY PER VIN
MODEL -------CLAIMS------- ------LABOR HOURS------ -----PARTS DOLLARS----- --------SUB/FRT-------- -----TOTAL DOLLARS----- -------
LINE DLR DST RGN DLR DST RGN DLR DST RGN DLR DST RGN DLR DST RGN % RGN
ALC 1.00 1.00 1.00 2.30 2.90 .88 1163.62 594.59 261.56 .00 .00 .00 1313.12 783.09 319.05 411.57
ALT 1.46 1.43 1.33 2.08 2.74 2.03 241.27 296.12 290.95 .52 .83 .90 376.91 474.97 423.85 88.93
ARM 1.50 1.17 1.37 1.55 2.25 2.14 170.53 409.78 369.25 .00 .00 4.15 271.28 555.99 512.24 52.96
GTR .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00
JKE 1.25 1.22 1.26 .78 2.13 1.94 495.21 218.64 398.60 .00 .00 3.00 545.59 356.92 527.77 103.38
KIX 1.09 1.32 1.36 2.49 1.87 1.56 215.06 387.25 525.51 .00 12.68 17.71 376.97 521.60 644.70 58.47
LEF 1.24 1.25 1.19 1.80 2.57 2.00 207.00 281.98 289.46 .00 .51 .81 324.00 449.52 420.16 77.11
MAX 1.31 1.46 1.40 2.18 3.09 2.38 183.99 302.72 324.01 6.56 1.41 1.41 332.29 504.84 480.12 69.21
MUR 1.30 1.38 1.34 1.79 2.24 2.11 231.31 298.89 353.24 1.28 2.03 1.74 348.70 446.72 492.33 70.83
NV 1.25 1.29 1.30 1.73 1.84 1.66 455.32 472.33 387.01 .00 .61 2.54 567.85 592.53 497.16 114.22
NVP 1.38 1.59 1.56 1.23 1.36 1.56 311.44 354.15 381.86 .00 5.43 4.38 391.07 448.22 487.61 80.20
NV2 1.28 1.45 1.40 1.49 1.90 1.67 454.42 374.50 387.39 .00 3.02 1.78 551.10 501.26 497.45 110.79
PTH 1.31 1.37 1.34 1.47 3.04 2.39 200.19 268.03 307.54 .00 .36 1.51 295.94 465.97 464.62 63.70
QST 1.38 1.33 1.26 5.36 4.79 3.40 452.24 313.57 324.59 .00 9.21 1.78 800.81 634.38 547.11 146.37
RGE 1.30 1.32 1.31 1.76 2.18 1.84 203.45 295.70 310.93 3.85 1.11 2.52 321.98 438.52 433.22 74.32
RGS 1.00 1.19 1.19 5.20 3.07 2.06 36.62 263.70 284.10 .00 .00 .00 374.62 463.17 417.99 89.62
RSP 1.37 1.23 1.21 1.49 1.09 1.12 213.54 165.85 199.78 .00 1.42 1.36 310.07 237.87 273.99 113.17

 

[rcasaletta]

I am not sure why I have not received any reply's, but it might be due to my ask so I'll revise it.

I believe that my issue is do to the 'For I = 1 To 30' statement. the code will go through 30 lines and then start again on the next section. Because this is a fixed get, there is eventual overlap in the data extract and incorrect data is pulled for the dealer.

Can someone assist me in writing the code to loop on the word 'TOTAL'? I believe my problem could be solved by extracting the data from the text and when the text line includes 'TOTAL', go the next dealer.

Thank you in advance.

 

[Joe4]

So, are you saying that you just want to extract lines that start with "TOTAL..."?
How many lines of data are in these text files?

I have done stuff like this, but have gone about it in a different manner.
I import the entire Text file in Excel, where each line is just one big long string.
Then loop or sort/filter through the records to delete everything that does not begin with "TOTAL..." (there are different ways to go about this).
Then, do a Text to Columns to split those lines up into multiple fields.

 

[rcasaletta]

I will try that , but just to be clear, I want everything prior to 'TOTAL' for each dealer.

 

[Joe4]

Then what are you trying to eliminate?
Just the TOTAL row, or some other lines too?

 

[rcasaletta]

I am trying to capture the data between Model Line and Total...ALC, ALT, ARM, etc.

 

[Joe4]

I would probably right code that loops through all the data, and deletes the line until it comes to a line where the first four characters are "LINE". Then it keeps looping through, keeping all lines, until it hits the line where the first four letters ate "TOTA", and deletes all lines until it finds the next line starting with "LINE", etc.

 

[Rick Rothstein]

I am trying to capture the data between Model Line and Total...ALC, ALT, ARM, etc.

For the sample text file you posted in Message #1 , can you show us the output you want from that text file?

Also, whatever is extracted, is it to remain as a text file or be placed on an Excel worksheet? If it is to be placed on a worksheet, is everything to remain in Column A or are the "fields" to be distributed into columns?

I am also wondering exactly what you meant in your thread title when you said "Extract data on page break"... what did you mean by "on page break"?

 

[rcasaletta]

Thank you for the suggestions. I was able to get the code to work by changing the
' For I = 1 To 30 Do While Not InStr(1, TextLine, "TOTAL", vbTextCompare) > 0
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
Loop