Macro Import selected lines from txt file

[alvbnp]

I have a large text file, I just need to import certain lines out from it.
Sample data:

account abc <- import this whole line
lot's of text here <- ignore
total 1: xxx <- import this whole line
total 2: xxx <- import this whole line

account def <- import this whole line
lot's of text here <- ignore
account def <- ignore
lot's of text here <- ignore
total 1: xxx <- import this whole line
total 2: xxx <- import this whole line

I just want to import the line start with 'account', 'total 1' and 'total 2'. From my sample data, import the whole line when found the word 'account', then look for the word 'total 1' and import the whole line, and find the word 'total 2' and import the whole line. After that, look for the word 'account' again and do the same thing again.

Thanks.

 

[pgc01]

Hi alvbnp

Sorry: did not read your post carefully

Kind regards
PGC

 

[pgc01]

Hi alvbnp

This code checks the beginning of each line for 'account', 'total 1' and 'total 2' in that sequence and imports the line.

You have to adjust the worksheet name (I've used Sheet2), the column ("A") and the pathname of the text file (C:\tmp\accounts.txt)

Please try:

Sub ReadAccounts()
Dim sLine As String, lRow As Long, iFN As Integer, iSt As Integer

With Worksheets("Sheet2")
    .Columns("A").ClearContents
    iFN = FreeFile
    Open "C:\tmp\accounts.txt" For Input As #iFN
    Do While Not EOF(iFN)
        Line Input #iFN, sLine
        If (iSt = 0 And Left(sLine, 7) = "account") Or _
           (iSt = 1 And Left(sLine, 7) = "total 1") Or _
           (iSt = 2 And Left(sLine, 7) = "total 2") Then
            iRow = iRow + 1
            .Range("A" & iRow) = sLine
            iSt = (iSt + 1) Mod 3
        End If
    Loop
End With
Close #iFN
End Sub

Hope this helps
PGC

EDIT: Replaced EOF statement

 

[pgc01]

Hi again

Please notice that I have edited the EOF statement.

 

[alvbnp]

Thanks. It works great.