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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

pgc01

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

Sorry: did not read your post carefully

Kind regards
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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,884
Hi again

Please notice that I have edited the EOF statement.
 

Forum statistics

Threads
1,141,022
Messages
5,703,788
Members
421,316
Latest member
Cyril Beki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top