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

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.)



wrote:

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

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

--
Regards,
Tom Ogilvy




"Dave Peterson" wrote in message
...
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.)



wrote:

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

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.



Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
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.)



wrote:

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

--

Dave Peterson

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

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