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

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

Frederic,

I just noticed that two of the "lngNextRow = ..." lines are missing the "."
(i.e. dot) prior to the "Range" object. (This will cause problems with where
the data will be placed, and you'll have unexpected results). Simply added
the dots to the following two lines:

lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1

Sorry for not reading my code over carefully prior to replying to the post.
Obviously, because I don't have the text file, the code is not tested.

Best,

Matt

"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

Hi Matthew

Thank you for the quick reply. I have added those extra dots but I'm
getting a runtime error '91' object variable or with block variable not set.
Error is highlighting on the first lngNextRow = .....

many thanks
Frederic

"Matthew Herbert" wrote:

Frederic,

I just noticed that two of the "lngNextRow = ..." lines are missing the "."
(i.e. dot) prior to the "Range" object. (This will cause problems with where
the data will be placed, and you'll have unexpected results). Simply added
the dots to the following two lines:

lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1

Sorry for not reading my code over carefully prior to replying to the post.
Obviously, because I don't have the text file, the code is not tested.

Best,

Matt

"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

After making the changes to the Range entries (to .Range), try changing this
section of code a bit to make another check:

Set Wks = Worksheets("ECI File Index")

Open strFilename For Input As vFileHandle


Change the Set statement and add a line of code to make it look like this

Set Wks = ThisWorkbook.Worksheets("ECI File Index")
Msgbox "Wks is now set to worksheet named [" & Wks.name & "]"

Open strFilename For Input As vFileHandle

Then try running it and seeing for sure that Wks got set to the worksheet.
Although I'd have expected an error at that Set statement if the sheet
doesn't exist. In looking at the code, I don't see any readily visible
problem that would give the error you reported; the Do and If blocks seem
correct and complete, which could have been a source of a false report of
that type if they aren't.



"bluewatermist" wrote:

Hi Matthew

Thank you for the quick reply. I have added those extra dots but I'm
getting a runtime error '91' object variable or with block variable not set.
Error is highlighting on the first lngNextRow = .....

many thanks
Frederic

"Matthew Herbert" wrote:

Frederic,

I just noticed that two of the "lngNextRow = ..." lines are missing the "."
(i.e. dot) prior to the "Range" object. (This will cause problems with where
the data will be placed, and you'll have unexpected results). Simply added
the dots to the following two lines:

lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1

Sorry for not reading my code over carefully prior to replying to the post.
Obviously, because I don't have the text file, the code is not tested.

Best,

Matt

"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

Hi

Setting Wks has worked but my other problem is if for example "PAYDETAILS"
doesn't exist in the text file how can I place on the worksheet at column L,
M or N "N/A".

I have tried the below code but when it starts checking each line in the
text file for PAYDETAILS and doesn't find it, the macro then adds "N/A" on
the next available line until it finds the word PAYDETAILS.

With Wks
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
..Range("L" & lngNextRow).Value = "N/A"

lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1
..Range("M" & lngNextRow).Value = "N/A"

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1
..Range("N" & lngNextRow).Value = "N/A"
End With


"JLatham" wrote:

After making the changes to the Range entries (to .Range), try changing this
section of code a bit to make another check:

Set Wks = Worksheets("ECI File Index")

Open strFilename For Input As vFileHandle


Change the Set statement and add a line of code to make it look like this

Set Wks = ThisWorkbook.Worksheets("ECI File Index")
Msgbox "Wks is now set to worksheet named [" & Wks.name & "]"

Open strFilename For Input As vFileHandle

Then try running it and seeing for sure that Wks got set to the worksheet.
Although I'd have expected an error at that Set statement if the sheet
doesn't exist. In looking at the code, I don't see any readily visible
problem that would give the error you reported; the Do and If blocks seem
correct and complete, which could have been a source of a false report of
that type if they aren't.



"bluewatermist" wrote:

Hi Matthew

Thank you for the quick reply. I have added those extra dots but I'm
getting a runtime error '91' object variable or with block variable not set.
Error is highlighting on the first lngNextRow = .....

many thanks
Frederic

"Matthew Herbert" wrote:

Frederic,

I just noticed that two of the "lngNextRow = ..." lines are missing the "."
(i.e. dot) prior to the "Range" object. (This will cause problems with where
the data will be placed, and you'll have unexpected results). Simply added
the dots to the following two lines:

lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1

Sorry for not reading my code over carefully prior to replying to the post.
Obviously, because I don't have the text file, the code is not tested.

Best,

Matt

"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

Frederic,

I'm implying that you still have a question with the code, but I don't know
what that question may be. I think the question might be something like "how
do I set up putting the "N/A" in column L, M, or N"? (If this is the case,
then there has to be some other criteria to decide how L would get "N/A" over
M or N, or M would get "N/A" over L or N, etc.). If you have a specific
question, please post back with that detailed question (and include your code
again to remove the guess work of what your code now looks like).

Best,

Matt

"bluewatermist" wrote:

Hi

Setting Wks has worked but my other problem is if for example "PAYDETAILS"
doesn't exist in the text file how can I place on the worksheet at column L,
M or N "N/A".

I have tried the below code but when it starts checking each line in the
text file for PAYDETAILS and doesn't find it, the macro then adds "N/A" on
the next available line until it finds the word PAYDETAILS.

With Wks
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
.Range("L" & lngNextRow).Value = "N/A"

lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1
.Range("M" & lngNextRow).Value = "N/A"

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1
.Range("N" & lngNextRow).Value = "N/A"
End With


"JLatham" wrote:

After making the changes to the Range entries (to .Range), try changing this
section of code a bit to make another check:

Set Wks = Worksheets("ECI File Index")

Open strFilename For Input As vFileHandle


Change the Set statement and add a line of code to make it look like this

Set Wks = ThisWorkbook.Worksheets("ECI File Index")
Msgbox "Wks is now set to worksheet named [" & Wks.name & "]"

Open strFilename For Input As vFileHandle

Then try running it and seeing for sure that Wks got set to the worksheet.
Although I'd have expected an error at that Set statement if the sheet
doesn't exist. In looking at the code, I don't see any readily visible
problem that would give the error you reported; the Do and If blocks seem
correct and complete, which could have been a source of a false report of
that type if they aren't.



"bluewatermist" wrote:

Hi Matthew

Thank you for the quick reply. I have added those extra dots but I'm
getting a runtime error '91' object variable or with block variable not set.
Error is highlighting on the first lngNextRow = .....

many thanks
Frederic

"Matthew Herbert" wrote:

Frederic,

I just noticed that two of the "lngNextRow = ..." lines are missing the "."
(i.e. dot) prior to the "Range" object. (This will cause problems with where
the data will be placed, and you'll have unexpected results). Simply added
the dots to the following two lines:

lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1

Sorry for not reading my code over carefully prior to replying to the post.
Obviously, because I don't have the text file, the code is not tested.

Best,

Matt

"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