Extract Data From Text File Using VBA

**rcasaletta** · 05-24-2019, 06:21 PM

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

**DocAElstein** · 05-25-2019, 01:31 AM

Hello rcasaletta,
Welcome to excelfox

At first glance, it looks as though you are already using and doing the sort of things like you need in this coding

In your current coding, you have a main Outer Loop which keeps going, ( it keeps looping), until you reach the End Of the text File. (I think EOF becomes True when you reach the End Of the text File ). In other words , it keeps Looping While you are Not at the End Of the text File...… This is the simplified form of it

Code:

  Do While Not EOF(1)' Outer Loop =======

  Loop' Outer Loop ======================

If I understand correctly, the current problem is that inside that loop, you have an Inner Loop which you are currently looping 30 times, which might not always be the number of lines until "Total" appears.
So this is the sort of thing you have currently in your existing coding, simplified

Code:

__Do While Not EOF(1) ' Outer Loop =======

_____For I = 1 To 30 ' Inner Loop ---


_____Next I ' Inner Loop ------------

__Loop' Outer Loop ======================

So what you need to do is change that inner loop so that it keeps going ( keeps Looping ) While you have Not got the word 'TOTAL' in the inputed text line.
So one way to do what you want would be something of this form, in other words,
replace
__the fixed number of loops _ For / Next _ loop ,
with a
__Do While / Loop _ loop

So this is the sort of thing you could do , simplified

Code:

__Do While Not EOF(1) ' Outer Loop ===================================================

_____Do While Not InStr(1, TextLine, "TOTAL", vbTextCompare) > 0 ' Inner Loop ----


_____Loop ' Inner Loop ------------------------------------------------------------

__Loop ' Outer Loop =================================================================

If you need more help, then best would be to give me..
_ A reduced size test data text file, ( change or make up any sensitive data like names , addresses etc). Just give enough data to demonstrate the typical scenarios
_ Give me an excel file with two worksheets. One worksheet is the Before, which I guess in your case will probably be almost empty. Then on the other worksheet, the After, fill it in by hand from the test text file data so that it looks exactly as you want the coding to do.
I will take a look then at it tomorrow, .. or Sunday :)

Alan

**rcasaletta** · 05-25-2019, 04:33 PM

**DocAElstein** · 05-26-2019, 01:25 PM

You are welcome and thanks for the feedback,
Alan

( P.s. Possibly you are aware of this, but just in case you don't know this, I thought I would mention it in passing...
You have a couple of lines like this
Dim FileToOpen, A(11) As Variant
Dim c, R As Long

What you have there is actually this, ( since variant is the default when you do not explicitly do it )
Dim FileToOpen As Variant, A(11) As Variant
Dim c As Variant, R As Long

It does make sense to have FileToOpen as Variant, because you may get a String type returned to it or a Boolean False. So a Variant is required to hold either of those two types.
But I am not sure what c is supposed to be in your coding. I may have missed something. If you wanted c to be dimensioned a Long type, then you would need to do this:
Dim c As Long, R As Long

If you do , as you did, this .._
Dim c, R As Long
_.. then c will then be dimensioned as a Variant)