Frederic,

I have inserted some code below that simply alters the ElseIf portion of
your code by using a nested If to search for "PAYDETAILS" and act accordingly
if "PAYDETAILS" is/isn't present. I also created a worksheet object to
remove the .Select from your code. (The .Select only slows things down and
is unnecessary). You didn't specify where the "N/A" is supposed to go, so I
simply commented that portion of the program for you to fill in accordingly.

As a side note, using your hard-coded values for the Mid function may
backfire on you if your text file format ever changes. You may want to
consider a way that would find the starting character (and length of text)
for you rather than using a hard-coded value. (For example, if your text
file is delimited, then using the delimiter is one way to leverage finding
the right spot and the right length).

Best,

Matthew Herbert

Sub readtext()

Dim strTextLine As String
Dim strFilename As String
Dim vFileHandle As Integer
Dim Wks As Worksheet
Dim lngNextRow As Long

strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt"

If Dir(strFilename) = "" Then
MsgBox "File Not Found"
Exit Sub
End If

vFileHandle = FreeFile()

Set Wks = Worksheets("ECI File Index")

Open strFilename For Input As vFileHandle

Do While Not EOF(vFileHandle)
Line Input #vFileHandle, strTextLine

If InStr(1, Left(strTextLine, 10), "CEG_HEADER") 0 Then
With Wks
lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1
.Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77)
End With
Else
If InStr(1, Left(strTextLine, 10), "PAYDETAILS") 0 Then
With Wks
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
.Range("L" & lngNextRow).Value = Mid(strTextLine, 11, 5)

lngNextRow = Range("M" & .Rows.Count).End(xlUp).Row + 1
.Range("M" & lngNextRow).Value = Mid(strTextLine, 16, 8)

lngNextRow = Range("N" & .Rows.Count).End(xlUp).Row + 1
.Range("N" & lngNextRow).Value = Mid(strTextLine, 24, 12)
End With
Else
'put the "N/A" where ever it needs to go. I'm not sure
' if the "N/A" goes into columns L, M, and N, or simply
' in one of the columns. You can fill this in as needed.
End If
End If
Loop
Close vFileHandle

End Sub

"bluewatermist" wrote:

Hi

I'm hoping you can help. I'm trying to search the next line of a text file
for a word called PAYDETAILS. If the word isn't there then in the worksheet
i need to place "N/A".

I have placed a shortened version of the program. I appreciate any help.

Regards
Frederic

sub readtext()

Dim strTextLine As String
Dim strFilename As String
Dim vFileHandle As Integer

strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt"

If Dir(strFilename) = "" Then
MsgBox ("File Not Found")
Exit Sub
End If

vFileHandle = FreeFile

Open strFilename For Input As vFileHandle

Do While Not EOF(vFileHandle)
Line Input #vFileHandle, strTextLine


If InStr(1, Left(strTextLine, 10), "CEG_HEADER") 0 Then
Sheets("ECI File Index").Select
NextRow = Range("O65536").End(xlUp).Row + 1
Range("O" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 77)

ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") 0 Then
Sheets("ECI File Index").Select
NextRow = Range("L65536").End(xlUp).Row + 1
Range("L" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 5)

NextRow = Range("M65536").End(xlUp).Row + 1
Range("M" & NextRow).Select
ActiveCell = Mid(strTextLine, 16, 8)

NextRow = Range("N65536").End(xlUp).Row + 1
Range("N" & NextRow).Select
ActiveCell = Mid(strTextLine, 24, 12)

end if
loop
Close vFileHandle

end sub