Macro Import selected lines from txt file

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi alvbnp

Sorry: did not read your post carefully

Kind regards
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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:

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi again

Please notice that I have edited the EOF statement.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,983
Messages
5,545,340
Members
410,678
Latest member
glasi
Top