Parsing a Text File

[rstroughair]

Good Evening,

I wonder if somebody could point me in the right direction with a query
I have?

I have a macro that currently parses a file looking for certain text
and when it finds it copies the string to a given cell in an Excel
file.

I need to adapt this to now look for the line that contains a certain
string of text and then import the following line of text to the cell
in Excel (where I can then manipulate it using text to columns).

Could someone please gie me a hint on how to accomplish this?

My code that needs altering is:

Open FName For Input As Fnum
i = 1

Do While Not EOF(Fnum)
If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then

' This is the bit I need to change to import the line following the
matching row....

Cells(3, 3).Value = sLine

End If


Thanks in advance,

Richard

 

[Dave Peterson]

Are you picking up a single line from the file or maybe multiple lines?

A single line:

Option Explicit
Sub testme()

Dim FName As String
Dim KeepTheNext As Boolean
Dim FNum As Long
Dim i As Long
Dim sLine As String

FName = "C:\my documents\excel\text1.txt"

FNum = FreeFile

Open FName For Input As FNum
i = 3

KeepTheNext = False
Do While Not EOF(FNum)
Input #FNum, sLine
If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
KeepTheNext = True
Else
If KeepTheNext = True Then
KeepTheNext = False
Cells(i, 3).Value = sLine
Exit Do 'if you just want one line from the file
'or go to the next row for the next match???
'i = i + 1
End If
End If
Loop

End Sub

If you wanted to pick up each of the lines after every match, get rid of the
"exit do" and just drop down a row to get ready for the next line.

(I use i as the row counter--I didn't see how it was used in your code.)

 

[Tom Ogilvy]

If the objective is to just do what you describe, you could simplify it to:
(assumes there will be a line after the line you are looking for)

Sub testme()
Dim FName As String
Dim FNum As Long
Dim sLine As String

FName = "C:\my documents\excel\text1.txt"

FNum = FreeFile

Open FName For Input As FNum
Do While Not EOF(FNum)
Input #FNum, sLine
If InStr(1, sLine, _
"NUE00001 GRAND TOTALS", _
vbTextCompare) > 0 Then
Input #FNum, sLine
Cells(3, 3).Value = sLine
End If
Loop
' now close the file
close #Fnum
End Sub

 

[Dave Peterson]

I wasn't sure and I wanted to make it easy enough to modify in either case.

Thanks for the correction to include the close statement.

 

[rstroughair]

Thanks for both of your answers, which worked great! However, could I
trouble you for one more thing?

I do indeed need to pick up multiple lines from the file. In most cases
these are the following line after the match, but on a couple of
occasions the line required in always two lines below the match. How do
I alter the code to cater for this (eg. assume in the given example the
match occurs on row 12 of the text file and I need to copy row 14 to
the Excel cell)?

Thanks again,

Richard

 

[rstroughair]

Thanks for your replies, but I seem to have got it working now!

This is the code I have used - is this the most efficient way?

Sub testme2()
Dim FName As String
Dim FNum As Long
Dim sLine As String
Dim TName As Variant

TName = Application.GetOpenFilename _
(fileFilter:="Text Files (*.txt),*.txt,All Files (*.*),*.*", _
Title:="Open Report")
If TName = False Then
MsgBox "You didn't select a file"
'Exit Sub
Cleanup
End
End If

FName = CStr(TName)

FNum = FreeFile

Open FName For Input As FNum
Do While Not EOF(FNum)
Input #FNum, sLine
If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
Line Input #FNum, sLine 'Match where
next line is needed
Cells(3, 3).Value = sLine
ElseIf InStr(1, sLine, "NUE00002 GRAND TOTALS", vbTextCompare) > 0
Then
Input #FNum, sLine
Line Input #FNum, sLine 'Match where
line 2 down is needed
Cells(4, 3).Value = sLine
End If
Loop
' now close the file
Close #FNum
End Sub

Richard

 

[Tom Ogilvy]

Sub testme2()
Dim FName As String
Dim FNum As Long
Dim sLine As String
Dim TName As Variant

TName = Application.GetOpenFilename _
(fileFilter:="Text Files (*.txt),*.txt,All Files (*.*),*.*", _
Title:="Open Report")
If TName = False Then
MsgBox "You didn't select a file"
'Exit Sub
Cleanup
End
End If

FName = CStr(TName)

FNum = FreeFile

Open FName For Input As FNum
Do While Not EOF(FNum)
Input #FNum, sLine
If InStr(1, sLine, "NUE00001 GRAND TOTALS", _
vbTextCompare) > 0 Then
Line Input #FNum, sLine 'Match where next line is needed
Cells(3, 3).Value = sLine
ElseIf InStr(1, sLine, "NUE00002 GRAND TOTALS", _
vbTextCompare) > 0 Then
Line Input #FNum, sLine
Line Input #FNum, sLine 'Match where line 2 down is needed
Cells(4, 3).Value = sLine
End If
Loop
' now close the file
Close #FNum
End Sub

It is unclear, but if you are done after you write the line, you can put in
Exit Do within Your If construct after you write the line. Also, I correct
one line that used Input instead of Line Input

 

[Dave Peterson]

Oops and thanks for that last correction, too <bg>.

 

[rstroughair]

Thanks again for your help Tom / Dave.

Richard