Read the next line in a text file

  • Thread starter Thread starter bluewatermist
  • Start date Start date
B

bluewatermist

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
 
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
 
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
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Hi Matthew

This problem was my first original question, but I have probably not stated
it clearly.
The text files have lots of other information which I don't require. What
is happening with the macro below is that if for example the macro doesn't
find on the next line the word "PAYDETAILS" it places "-----" on column L, or
M, or N. Therefore if the information retreived made only 4 rows on the work
sheet, columns L, or M or N used up 57 rows. I have place an example below
and the code.

Hope i have made it clearer
Frederic

$773.75 17092009 SUPERLIFE PTY ----- ----- -----
$1015.90 17092009 SUPERLIFE PTY ----- ----- -----
$315.00 11092009 SUPERLIFE PTY ----- ----- -----
$142.50 17092009 SUPERLIFE PTY ----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $77.37
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $101.59
----- ----- -----
----- ----- -----


Sub ReadText()

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

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

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

vFileHandle = FreeFile

newfilepath = Right(strFilename, 31)

Open strFilename For Input As vFileHandle

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

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then
With Wks
NextRow = Range("O65536").End(xlUp).Row + 1
Range("O" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 77)
End With
ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then
With Wks
NextRow = Range("C65536").End(xlUp).Row + 1
Range("C" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 44)
End With

ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then
With Wks
NextRow = Range("D65536").End(xlUp).Row + 1
Range("D" & NextRow).Select
ActiveCell = Mid(strTextLine, 148, 10)

NextRow = Range("E65536").End(xlUp).Row + 1
Range("E" & NextRow).Select
ActiveCell = Mid(strTextLine, 191, 8)
End With
ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then
With Wks
NextRow = Range("K65536").End(xlUp).Row + 1
Range("K" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 76)
End With
ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then
With Wks
NextRow = Range("G65536").End(xlUp).Row + 1
Range("G" & NextRow).Select
ActiveCell = Mid(strTextLine, 31, 11)

NextRow = Range("H65536").End(xlUp).Row + 1
Range("H" & NextRow).Select
ActiveCell = Mid(strTextLine, 51, 76)

If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Select
Selection.Value = "-----"
ActiveCell.Offset(0, -13).Select
Selection.Value = "--"
ActiveCell.Offset(0, -1).Select
Selection.Value = newfilepath
ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> ""
Then
ActiveCell.Offset(0, -6).Select
Selection.Value = "Y"
ActiveCell.Offset(0, -1).Select
Selection.Value = newfilepath
End If
End With
ElseIf InStr(1, Left(strTextLine, 10), "SPRCONTBTN") > 0 Then
With Wks
NextRow = Range("I65536").End(xlUp).Row + 1
Range("I" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 13)

NextRow = Range("J65536").End(xlUp).Row + 1
Range("J" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 8)
End With
ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then
With Wks
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 With
else
Else
With Wks
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
..Range("L" & lngNextRow).Value = "-----"

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

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

Loop

Close vFileHandle

End Sub


Matthew Herbert said:
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 said:
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 said:
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.



:

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

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Fredric,

I've altered your code a little bit. I added a Case statement instead of
your If, ElseIf statements. (I think that the Case statement is a bit easier
to read. If you haven't used a Case statement before, the VBE Help
documentation has some good information on how it works. It acts very
similar to the If...Then statement. Also, take note of the comments listed
in the code below).

I'm still not 100% sure what you are trying to achieve. What your code is
set up to do is the following:
1. Test for "CEG_HEADER". If it exists, do something and move to the next
line of the text file.
2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME"
exists, do something and move to the next line of the text file.
3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc.
.....
If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR",
"SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----".

Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"]
isn't there then in the worksheet I need to place 'N/A'," as noted in your
first post. I say this because, as noted above, if "PAYDETAILS" does NOT
exist (meaning that none of the other words exist either and the code has
reached the "Case Else" section), then insert the "----".

Are you familiar with the Debugging tools inside VBE? If not, then at least
use the following to test and evaluate your code:
In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow
you to evaluate your code one line at a time. As you evaluate your code
(i.e. press the F8 key) you will see a yellow line highlight syntax. As you
press F8, you will see the yellow line advance one line at a time. I think
that doing this simple exercise will help you see how your logic is behaving
and will hopefully help you see where/why the logic is not behaving as you
anticipated. Otherwise, you'll have to really spell out what you are looking
for.

For example, I don't know what you mean or intend when you say "...the
information retreived made only 4 rows on the worksheet, [but] columns L, or
M, or N used up 57 rows." Do you mean that columns L, or M, or N should have
used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2
rows only instead of spread out and separated by "----"?). How is the data
supposed to look? (For example, assuming from your post that the dollar
column is "A" and the subsequent columns move in alphabetical order [i.e. A1
= $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 =
----, etc. ] there should be the four rows of data (A1:C4) as shown in the
post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 =
$101.59; F3:G4 = ----).

Best,

Matt

Sub ReadText()

Dim strTextLine As String
Dim strFilename As String
Dim strNewFilepath As String
Dim intFileHandle As Integer
Dim Wks As Worksheet
Dim lngNextRow As Long

'should this file have a ".txt" extension? If so, then
' you'll have to adjust the suggestion for strNewFilepath
' listed below
strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570"

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

intFileHandle = FreeFile

'consider the following instead, which finds the first space,
' working right to left, in order to get the "new" filename:
'strNewFilepath = Right(strFilename, Len(strFilename) -
InStrRev(strFilename, " "))
strNewFilepath = Right(strFilename, 31)

Open strFilename For Input As intFileHandle

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

With Wks
Do While Not EOF(intFileHandle)
Line Input #intFileHandle, strTextLine

Select Case Left(strTextLine, 10)
Case "CEG_HEADER"
lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1
.Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77)

Case "FILENAME"
lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44)

Case "INTRCHGHDR"
lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1
.Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10)

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

Case "RECIPNTDTL"
lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1
.Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76)

Case "SPRPRODHDR"
lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1
.Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11)

lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
.Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76)

With .Range("H" & lngNextRow)
If .Offset(0, 7) = "" Then
.Offset(0, 7).Value = "-----"
'double check this Offset
.Offset(0, -6).Value = "--"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath

ElseIf .Offset(0, 7) <> "-----" And .Offset(0, 7) <> ""
Then
.Offset(0, -6).Value = "Y"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath
End If
End With

Case "SPRCONTBTN"
lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1
.Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13)

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

Case "PAYDETAILS"
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)

Case Else
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
.Range("L" & lngNextRow).Value = "-----"

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

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1
.Range("N" & lngNextRow).Value = "-----"
End Select
Loop
End With

Close intFileHandle

End Sub


bluewatermist said:
Hi Matthew

This problem was my first original question, but I have probably not stated
it clearly.
The text files have lots of other information which I don't require. What
is happening with the macro below is that if for example the macro doesn't
find on the next line the word "PAYDETAILS" it places "-----" on column L, or
M, or N. Therefore if the information retreived made only 4 rows on the work
sheet, columns L, or M or N used up 57 rows. I have place an example below
and the code.

Hope i have made it clearer
Frederic

$773.75 17092009 SUPERLIFE PTY ----- ----- -----
$1015.90 17092009 SUPERLIFE PTY ----- ----- -----
$315.00 11092009 SUPERLIFE PTY ----- ----- -----
$142.50 17092009 SUPERLIFE PTY ----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $77.37
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $101.59
----- ----- -----
----- ----- -----


Sub ReadText()

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

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

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

vFileHandle = FreeFile

newfilepath = Right(strFilename, 31)

Open strFilename For Input As vFileHandle

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

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then
With Wks
NextRow = Range("O65536").End(xlUp).Row + 1
Range("O" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 77)
End With
ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then
With Wks
NextRow = Range("C65536").End(xlUp).Row + 1
Range("C" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 44)
End With

ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then
With Wks
NextRow = Range("D65536").End(xlUp).Row + 1
Range("D" & NextRow).Select
ActiveCell = Mid(strTextLine, 148, 10)

NextRow = Range("E65536").End(xlUp).Row + 1
Range("E" & NextRow).Select
ActiveCell = Mid(strTextLine, 191, 8)
End With
ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then
With Wks
NextRow = Range("K65536").End(xlUp).Row + 1
Range("K" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 76)
End With
ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then
With Wks
NextRow = Range("G65536").End(xlUp).Row + 1
Range("G" & NextRow).Select
ActiveCell = Mid(strTextLine, 31, 11)

NextRow = Range("H65536").End(xlUp).Row + 1
Range("H" & NextRow).Select
ActiveCell = Mid(strTextLine, 51, 76)

If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Select
Selection.Value = "-----"
ActiveCell.Offset(0, -13).Select
Selection.Value = "--"
ActiveCell.Offset(0, -1).Select
Selection.Value = newfilepath
ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> ""
Then
ActiveCell.Offset(0, -6).Select
Selection.Value = "Y"
ActiveCell.Offset(0, -1).Select
Selection.Value = newfilepath
End If
End With
ElseIf InStr(1, Left(strTextLine, 10), "SPRCONTBTN") > 0 Then
With Wks
NextRow = Range("I65536").End(xlUp).Row + 1
Range("I" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 13)

NextRow = Range("J65536").End(xlUp).Row + 1
Range("J" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 8)
End With
ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then
With Wks
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 With
else
Else
With Wks
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
.Range("L" & lngNextRow).Value = "-----"

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

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

Loop

Close vFileHandle

End Sub


Matthew Herbert said:
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 said:
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


:

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.



:

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

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Hi Matthew

Thank you for persevering with me and i really appreciate how you have
shown me a simpler and better way to write the code.

My main problem still exists though. As it is reading line by line I need
the following to occur. Usely in the text file the word SPRCONTBTN is before
PAYDETAILS, but sometimes there won't be the word PAYDETAILS in the next
line. So then I am trying to make that if the next line doesn't have
PAYDETAILS, then in the worksheet there will be either "N/A" or "-----".
Currently it is reading every line and if the next line doesn't contain
PAYDETAILS it is adding "-----" in the worksheet. If the text file has say
50 lines it will start placing "-----" on the worksheets in the relevant
columns until it finds the word PAYDETAILS and then it will add payment type,
date and dollar amount.

I'm hoping this is clearer.
Regards
Frederic

Matthew Herbert said:
Fredric,

I've altered your code a little bit. I added a Case statement instead of
your If, ElseIf statements. (I think that the Case statement is a bit easier
to read. If you haven't used a Case statement before, the VBE Help
documentation has some good information on how it works. It acts very
similar to the If...Then statement. Also, take note of the comments listed
in the code below).

I'm still not 100% sure what you are trying to achieve. What your code is
set up to do is the following:
1. Test for "CEG_HEADER". If it exists, do something and move to the next
line of the text file.
2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME"
exists, do something and move to the next line of the text file.
3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc.
....
If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR",
"SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----".

Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"]
isn't there then in the worksheet I need to place 'N/A'," as noted in your
first post. I say this because, as noted above, if "PAYDETAILS" does NOT
exist (meaning that none of the other words exist either and the code has
reached the "Case Else" section), then insert the "----".

Are you familiar with the Debugging tools inside VBE? If not, then at least
use the following to test and evaluate your code:
In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow
you to evaluate your code one line at a time. As you evaluate your code
(i.e. press the F8 key) you will see a yellow line highlight syntax. As you
press F8, you will see the yellow line advance one line at a time. I think
that doing this simple exercise will help you see how your logic is behaving
and will hopefully help you see where/why the logic is not behaving as you
anticipated. Otherwise, you'll have to really spell out what you are looking
for.

For example, I don't know what you mean or intend when you say "...the
information retreived made only 4 rows on the worksheet, [but] columns L, or
M, or N used up 57 rows." Do you mean that columns L, or M, or N should have
used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2
rows only instead of spread out and separated by "----"?). How is the data
supposed to look? (For example, assuming from your post that the dollar
column is "A" and the subsequent columns move in alphabetical order [i.e. A1
= $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 =
----, etc. ] there should be the four rows of data (A1:C4) as shown in the
post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 =
$101.59; F3:G4 = ----).

Best,

Matt

Sub ReadText()

Dim strTextLine As String
Dim strFilename As String
Dim strNewFilepath As String
Dim intFileHandle As Integer
Dim Wks As Worksheet
Dim lngNextRow As Long

'should this file have a ".txt" extension? If so, then
' you'll have to adjust the suggestion for strNewFilepath
' listed below
strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570"

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

intFileHandle = FreeFile

'consider the following instead, which finds the first space,
' working right to left, in order to get the "new" filename:
'strNewFilepath = Right(strFilename, Len(strFilename) -
InStrRev(strFilename, " "))
strNewFilepath = Right(strFilename, 31)

Open strFilename For Input As intFileHandle

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

With Wks
Do While Not EOF(intFileHandle)
Line Input #intFileHandle, strTextLine

Select Case Left(strTextLine, 10)
Case "CEG_HEADER"
lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1
.Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77)

Case "FILENAME"
lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44)

Case "INTRCHGHDR"
lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1
.Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10)

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

Case "RECIPNTDTL"
lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1
.Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76)

Case "SPRPRODHDR"
lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1
.Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11)

lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
.Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76)

With .Range("H" & lngNextRow)
If .Offset(0, 7) = "" Then
.Offset(0, 7).Value = "-----"
'double check this Offset
.Offset(0, -6).Value = "--"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath

ElseIf .Offset(0, 7) <> "-----" And .Offset(0, 7) <> ""
Then
.Offset(0, -6).Value = "Y"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath
End If
End With

Case "SPRCONTBTN"
lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1
.Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13)

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

Case "PAYDETAILS"
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)

Case Else
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
.Range("L" & lngNextRow).Value = "-----"

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

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1
.Range("N" & lngNextRow).Value = "-----"
End Select
Loop
End With

Close intFileHandle

End Sub


bluewatermist said:
Hi Matthew

This problem was my first original question, but I have probably not stated
it clearly.
The text files have lots of other information which I don't require. What
is happening with the macro below is that if for example the macro doesn't
find on the next line the word "PAYDETAILS" it places "-----" on column L, or
M, or N. Therefore if the information retreived made only 4 rows on the work
sheet, columns L, or M or N used up 57 rows. I have place an example below
and the code.

Hope i have made it clearer
Frederic

$773.75 17092009 SUPERLIFE PTY ----- ----- -----
$1015.90 17092009 SUPERLIFE PTY ----- ----- -----
$315.00 11092009 SUPERLIFE PTY ----- ----- -----
$142.50 17092009 SUPERLIFE PTY ----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $77.37
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $101.59
----- ----- -----
----- ----- -----


Sub ReadText()

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

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

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

vFileHandle = FreeFile

newfilepath = Right(strFilename, 31)

Open strFilename For Input As vFileHandle

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

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then
With Wks
NextRow = Range("O65536").End(xlUp).Row + 1
Range("O" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 77)
End With
ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then
With Wks
NextRow = Range("C65536").End(xlUp).Row + 1
Range("C" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 44)
End With

ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then
With Wks
NextRow = Range("D65536").End(xlUp).Row + 1
Range("D" & NextRow).Select
ActiveCell = Mid(strTextLine, 148, 10)

NextRow = Range("E65536").End(xlUp).Row + 1
Range("E" & NextRow).Select
ActiveCell = Mid(strTextLine, 191, 8)
End With
ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then
With Wks
NextRow = Range("K65536").End(xlUp).Row + 1
Range("K" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 76)
End With
ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then
With Wks
NextRow = Range("G65536").End(xlUp).Row + 1
Range("G" & NextRow).Select
ActiveCell = Mid(strTextLine, 31, 11)

NextRow = Range("H65536").End(xlUp).Row + 1
Range("H" & NextRow).Select
ActiveCell = Mid(strTextLine, 51, 76)

If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Select
Selection.Value = "-----"
ActiveCell.Offset(0, -13).Select
Selection.Value = "--"
ActiveCell.Offset(0, -1).Select
Selection.Value = newfilepath
ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> ""
Then
ActiveCell.Offset(0, -6).Select
Selection.Value = "Y"
ActiveCell.Offset(0, -1).Select
Selection.Value = newfilepath
End If
End With
ElseIf InStr(1, Left(strTextLine, 10), "SPRCONTBTN") > 0 Then
With Wks
NextRow = Range("I65536").End(xlUp).Row + 1
Range("I" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 13)

NextRow = Range("J65536").End(xlUp).Row + 1
Range("J" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 8)
End With
ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then
With Wks
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
Click to expand...
Click to expand...
 
Frederic,

This last post is getting closer to being much more clear (or at least I now
have an idea of what you are trying to do). Because I don't have the actual
text file, the code below is written on the fly. As a result, it's not
tested, and it isn't necessarily the "most efficient" way to do things (but
it is a good way to illustrate some additional programming concepts that you
may have not seen, namely arrays). I commented the code, so be sure to read
those comments as you look at the code below.

You'll have to check the logic inside the "Case "SPRCONTBTN"" syntax because
I have no way of knowing what you are truly looking for (i.e. check the
If...Then block of code). I simply added the "new" code in this section
based on your recent post. Your statement of "...usually in the text file
the word SPRCONTBTN is before PAYDETAILS..." is a bit bothersome to me. Do
you need to test for "PAYDETAILS" as the next line for each of the Case
conditions? If this is what you need, then let me know and we'll create an
additional procedure/function to systematically make this check very easy for
each of the Case conditions. However, you'll at least have one example
within "SPRCONTBTN" to work with (and test) for now.

In addition to the change within "Case "SPRCONTBTN"", please ensure that the
"Case "PAYDETAILS"" is doing what you anticipate. Also, you'll have to
determine if you now need code in the "Case Else" statement. (It's okay if
you don't have anything in the "Case Else" statement). You'll notice that I
left a simple comment in the Case Else section. (Again, use F8 repeatedly to
see how the logic of the program is behaving).

Let me know if this gets you closer to where you need to be.

Best,

Matt

Sub ReadText()

Dim strTextLine As String
Dim strFilename As String
Dim strNewFilepath As String
Dim intFileHandle As Integer
Dim Wks As Worksheet
Dim lngNextRow As Long
Dim strArr() As String
Dim strNext As String
Dim lngCnt As Long

'should this file have a ".txt" extension? If so, then
' you'll have to adjust the suggestion for strNewFilepath
' listed below
strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570"

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

intFileHandle = FreeFile

'consider the following instead, which finds the first space,
' working right to left, in order to get the "new" filename:
'strNewFilepath = Right(strFilename, Len(strFilename) - _
' InStrRev(strFilename, " "))
strNewFilepath = Right(strFilename, 31)

Open strFilename For Input As intFileHandle

'load the text file data into an array. Each line
' will be an item in the array. This will allow
' you to access any line of the text file at any
' time in order to determine if "PAYDETAILS"
' exists. (See the For...Next loop below).
lngCnt = 0
Do While Not EOF(intFileHandle)
Line Input #intFileHandle, strTextLine
ReDim Preserve strArr(lngCnt)
strArr(lngCnt) = strTextLine
lngCnt = lngCnt + 1
Loop
Close intFileHandle

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

With Wks

'loop through each of the items in the array (i.e.
' loop through each of the text file lines)
For lngCnt = LBound(strArr) To UBound(strArr)

'evaluate the first 10 characters of each item
Select Case Left(strArr(lngCnt), 10)

Case "CEG_HEADER"
lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1
.Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77)

Case "FILENAME"
lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44)

Case "INTRCHGHDR"
lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1
.Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10)

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

Case "RECIPNTDTL"
lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1
.Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76)

Case "SPRPRODHDR"
lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1
.Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11)

lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
.Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76)

With .Range("H" & lngNextRow)
If .Offset(0, 7) = "" Then
.Offset(0, 7).Value = "-----"
'double check this Offset
.Offset(0, -6).Value = "--"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath

ElseIf .Offset(0, 7) <> "-----" And _
.Offset(0, 7) <> "" Then
.Offset(0, -6).Value = "Y"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath
End If
End With

Case "SPRCONTBTN"
lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1
.Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13)

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

'----------------------------------------------------
'NEW CODE BLOCK FOR TESTING THE "NEXT" LINE FOR
' PAYDETAILS

'if you are in the last position of the array, then
' trying to get the (last position + 1) will throw
' an error because (last position + 1) doesn't exist.
' (last position + 1) pertains to the
' strNext = strArr(lngCnt + 1) below.
If lngCnt <> UBound(strArr) Then

strNext = strArr(lngCnt + 1)

'if the next line does NOT have "PAYDETAILS", insert
' "----" into the desired cells
If Left(strNext, 10) <> "PAYDETAILS" Then
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row
+ 1
.Range("L" & lngNextRow).Value = "-----"

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

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

Case "PAYDETAILS"
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)

Case Else
'you need to decide if you need to add code in here
End Select
Next lngCnt
End With

End Sub


bluewatermist said:
Hi Matthew

Thank you for persevering with me and i really appreciate how you have
shown me a simpler and better way to write the code.

My main problem still exists though. As it is reading line by line I need
the following to occur. Usely in the text file the word SPRCONTBTN is before
PAYDETAILS, but sometimes there won't be the word PAYDETAILS in the next
line. So then I am trying to make that if the next line doesn't have
PAYDETAILS, then in the worksheet there will be either "N/A" or "-----".
Currently it is reading every line and if the next line doesn't contain
PAYDETAILS it is adding "-----" in the worksheet. If the text file has say
50 lines it will start placing "-----" on the worksheets in the relevant
columns until it finds the word PAYDETAILS and then it will add payment type,
date and dollar amount.

I'm hoping this is clearer.
Regards
Frederic

Matthew Herbert said:
Fredric,

I've altered your code a little bit. I added a Case statement instead of
your If, ElseIf statements. (I think that the Case statement is a bit easier
to read. If you haven't used a Case statement before, the VBE Help
documentation has some good information on how it works. It acts very
similar to the If...Then statement. Also, take note of the comments listed
in the code below).

I'm still not 100% sure what you are trying to achieve. What your code is
set up to do is the following:
1. Test for "CEG_HEADER". If it exists, do something and move to the next
line of the text file.
2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME"
exists, do something and move to the next line of the text file.
3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc.
....
If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR",
"SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----".

Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"]
isn't there then in the worksheet I need to place 'N/A'," as noted in your
first post. I say this because, as noted above, if "PAYDETAILS" does NOT
exist (meaning that none of the other words exist either and the code has
reached the "Case Else" section), then insert the "----".

Are you familiar with the Debugging tools inside VBE? If not, then at least
use the following to test and evaluate your code:
In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow
you to evaluate your code one line at a time. As you evaluate your code
(i.e. press the F8 key) you will see a yellow line highlight syntax. As you
press F8, you will see the yellow line advance one line at a time. I think
that doing this simple exercise will help you see how your logic is behaving
and will hopefully help you see where/why the logic is not behaving as you
anticipated. Otherwise, you'll have to really spell out what you are looking
for.

For example, I don't know what you mean or intend when you say "...the
information retreived made only 4 rows on the worksheet, [but] columns L, or
M, or N used up 57 rows." Do you mean that columns L, or M, or N should have
used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2
rows only instead of spread out and separated by "----"?). How is the data
supposed to look? (For example, assuming from your post that the dollar
column is "A" and the subsequent columns move in alphabetical order [i.e. A1
= $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 =
----, etc. ] there should be the four rows of data (A1:C4) as shown in the
post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 =
$101.59; F3:G4 = ----).

Best,

Matt

Sub ReadText()

Dim strTextLine As String
Dim strFilename As String
Dim strNewFilepath As String
Dim intFileHandle As Integer
Dim Wks As Worksheet
Dim lngNextRow As Long

'should this file have a ".txt" extension? If so, then
' you'll have to adjust the suggestion for strNewFilepath
' listed below
strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570"

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

intFileHandle = FreeFile

'consider the following instead, which finds the first space,
' working right to left, in order to get the "new" filename:
'strNewFilepath = Right(strFilename, Len(strFilename) -
InStrRev(strFilename, " "))
strNewFilepath = Right(strFilename, 31)

Open strFilename For Input As intFileHandle

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

With Wks
Do While Not EOF(intFileHandle)
Line Input #intFileHandle, strTextLine

Select Case Left(strTextLine, 10)
Case "CEG_HEADER"
lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1
.Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77)

Case "FILENAME"
lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44)

Case "INTRCHGHDR"
lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1
.Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10)

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

Case "RECIPNTDTL"
lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1
.Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76)

Case "SPRPRODHDR"
lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1
.Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11)

lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
.Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76)

With .Range("H" & lngNextRow)
If .Offset(0, 7) = "" Then
.Offset(0, 7).Value = "-----"
'double check this Offset
.Offset(0, -6).Value = "--"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath

ElseIf .Offset(0, 7) <> "-----" And .Offset(0, 7) <> ""
Then
.Offset(0, -6).Value = "Y"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath
End If
End With

Case "SPRCONTBTN"
lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1
.Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13)

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

Case "PAYDETAILS"
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)

Case Else
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
.Range("L" & lngNextRow).Value = "-----"

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

lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1
.Range("N" & lngNextRow).Value = "-----"
End Select
Loop
End With

Close intFileHandle

End Sub


bluewatermist said:
Hi Matthew

This problem was my first original question, but I have probably not stated
it clearly.
The text files have lots of other information which I don't require. What
is happening with the macro below is that if for example the macro doesn't
find on the next line the word "PAYDETAILS" it places "-----" on column L, or
M, or N. Therefore if the information retreived made only 4 rows on the work
sheet, columns L, or M or N used up 57 rows. I have place an example below
and the code.

Hope i have made it clearer
Frederic

$773.75 17092009 SUPERLIFE PTY ----- ----- -----
$1015.90 17092009 SUPERLIFE PTY ----- ----- -----
$315.00 11092009 SUPERLIFE PTY ----- ----- -----
$142.50 17092009 SUPERLIFE PTY ----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $77.37
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
----- ----- -----
CHQ 12082009 $101.59
----- ----- -----
----- ----- -----


Sub ReadText()

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

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

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

vFileHandle = FreeFile

newfilepath = Right(strFilename, 31)

Open strFilename For Input As vFileHandle

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

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then
With Wks
NextRow = Range("O65536").End(xlUp).Row + 1
Range("O" & NextRow).Select
ActiveCell = Mid(strTextLine, 40, 77)
End With
ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then
With Wks
NextRow = Range("C65536").End(xlUp).Row + 1
Range("C" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 44)
End With

ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then
With Wks
NextRow = Range("D65536").End(xlUp).Row + 1
Range("D" & NextRow).Select
ActiveCell = Mid(strTextLine, 148, 10)

NextRow = Range("E65536").End(xlUp).Row + 1
Range("E" & NextRow).Select
ActiveCell = Mid(strTextLine, 191, 8)
End With
ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then
With Wks
NextRow = Range("K65536").End(xlUp).Row + 1
Range("K" & NextRow).Select
ActiveCell = Mid(strTextLine, 11, 76)
End With
ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then
With Wks
NextRow = Range("G65536").End(xlUp).Row + 1
Range("G" & NextRow).Select
ActiveCell = Mid(strTextLine, 31, 11)

NextRow = Range("H65536").End(xlUp).Row + 1
Range("H" & NextRow).Select
ActiveCell = Mid(strTextLine, 51, 76)

If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Select
Selection.Value = "-----"
ActiveCell.Offset(0, -13).Select
Selection.Value = "--"
ActiveCell.Offset(0, -1).Select
Selection.Value = newfilepath
ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> ""
Then
ActiveCell.Offset(0, -6).Select
Selection.Value = "Y"
Click to expand...
Click to expand...
Click to expand...
 
Hi Matthew

Thank you so much for your patience. This is exactly what I need. The only
change I made was to the below code from strTextLine to strArr(IngCnt)

..Range("O" & lngNextRow).Value = Mid(strArr(lngCnt), 40, 77)

Other Case conditions besides PAYDETAILS sometimes do not occur. Is the new
code block that you gave me the best way for all the other Case conditions? I
really just need to check if the Case conditions exist in the text file and
if they don't then add "-----" in the worksheet under the corresponding
column.

Thanks for your help :-)
Frederic

Matthew Herbert said:
Frederic,

This last post is getting closer to being much more clear (or at least I now
have an idea of what you are trying to do). Because I don't have the actual
text file, the code below is written on the fly. As a result, it's not
tested, and it isn't necessarily the "most efficient" way to do things (but
it is a good way to illustrate some additional programming concepts that you
may have not seen, namely arrays). I commented the code, so be sure to read
those comments as you look at the code below.

You'll have to check the logic inside the "Case "SPRCONTBTN"" syntax because
I have no way of knowing what you are truly looking for (i.e. check the
If...Then block of code). I simply added the "new" code in this section
based on your recent post. Your statement of "...usually in the text file
the word SPRCONTBTN is before PAYDETAILS..." is a bit bothersome to me. Do
you need to test for "PAYDETAILS" as the next line for each of the Case
conditions? If this is what you need, then let me know and we'll create an
additional procedure/function to systematically make this check very easy for
each of the Case conditions. However, you'll at least have one example
within "SPRCONTBTN" to work with (and test) for now.

In addition to the change within "Case "SPRCONTBTN"", please ensure that the
"Case "PAYDETAILS"" is doing what you anticipate. Also, you'll have to
determine if you now need code in the "Case Else" statement. (It's okay if
you don't have anything in the "Case Else" statement). You'll notice that I
left a simple comment in the Case Else section. (Again, use F8 repeatedly to
see how the logic of the program is behaving).

Let me know if this gets you closer to where you need to be.

Best,

Matt

Sub ReadText()

Dim strTextLine As String
Dim strFilename As String
Dim strNewFilepath As String
Dim intFileHandle As Integer
Dim Wks As Worksheet
Dim lngNextRow As Long
Dim strArr() As String
Dim strNext As String
Dim lngCnt As Long

'should this file have a ".txt" extension? If so, then
' you'll have to adjust the suggestion for strNewFilepath
' listed below
strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570"

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

intFileHandle = FreeFile

'consider the following instead, which finds the first space,
' working right to left, in order to get the "new" filename:
'strNewFilepath = Right(strFilename, Len(strFilename) - _
' InStrRev(strFilename, " "))
strNewFilepath = Right(strFilename, 31)

Open strFilename For Input As intFileHandle

'load the text file data into an array. Each line
' will be an item in the array. This will allow
' you to access any line of the text file at any
' time in order to determine if "PAYDETAILS"
' exists. (See the For...Next loop below).
lngCnt = 0
Do While Not EOF(intFileHandle)
Line Input #intFileHandle, strTextLine
ReDim Preserve strArr(lngCnt)
strArr(lngCnt) = strTextLine
lngCnt = lngCnt + 1
Loop
Close intFileHandle

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

With Wks

'loop through each of the items in the array (i.e.
' loop through each of the text file lines)
For lngCnt = LBound(strArr) To UBound(strArr)

'evaluate the first 10 characters of each item
Select Case Left(strArr(lngCnt), 10)

Case "CEG_HEADER"
lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1
.Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77)

Case "FILENAME"
lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44)

Case "INTRCHGHDR"
lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1
.Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10)

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

Case "RECIPNTDTL"
lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1
.Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76)

Case "SPRPRODHDR"
lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1
.Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11)

lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
.Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76)

With .Range("H" & lngNextRow)
If .Offset(0, 7) = "" Then
.Offset(0, 7).Value = "-----"
'double check this Offset
.Offset(0, -6).Value = "--"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath

ElseIf .Offset(0, 7) <> "-----" And _
.Offset(0, 7) <> "" Then
.Offset(0, -6).Value = "Y"
'double check this Offset
.Offset(0, -7).Value = strNewFilepath
End If
End With

Case "SPRCONTBTN"
lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1
.Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13)

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

'----------------------------------------------------
'NEW CODE BLOCK FOR TESTING THE "NEXT" LINE FOR
' PAYDETAILS

'if you are in the last position of the array, then
' trying to get the (last position + 1) will throw
' an error because (last position + 1) doesn't exist.
' (last position + 1) pertains to the
' strNext = strArr(lngCnt + 1) below.
If lngCnt <> UBound(strArr) Then

strNext = strArr(lngCnt + 1)

'if the next line does NOT have "PAYDETAILS", insert
' "----" into the desired cells
If Left(strNext, 10) <> "PAYDETAILS" Then
lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row
+ 1
.Range("L" & lngNextRow).Value = "-----"

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

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

Case "PAYDETAILS"
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)

Case Else
'you need to decide if you need to add code in here
End Select
Next lngCnt
End With

End Sub


bluewatermist said:
Hi Matthew

Thank you for persevering with me and i really appreciate how you have
shown me a simpler and better way to write the code.

My main problem still exists though. As it is reading line by line I need
the following to occur. Usely in the text file the word SPRCONTBTN is before
PAYDETAILS, but sometimes there won't be the word PAYDETAILS in the next
line. So then I am trying to make that if the next line doesn't have
PAYDETAILS, then in the worksheet there will be either "N/A" or "-----".
Currently it is reading every line and if the next line doesn't contain
PAYDETAILS it is adding "-----" in the worksheet. If the text file has say
50 lines it will start placing "-----" on the worksheets in the relevant
columns until it finds the word PAYDETAILS and then it will add payment type,
date and dollar amount.

I'm hoping this is clearer.
Regards
Frederic

Matthew Herbert said:
Fredric,

I've altered your code a little bit. I added a Case statement instead of
your If, ElseIf statements. (I think that the Case statement is a bit easier
to read. If you haven't used a Case statement before, the VBE Help
documentation has some good information on how it works. It acts very
similar to the If...Then statement. Also, take note of the comments listed
in the code below).

I'm still not 100% sure what you are trying to achieve. What your code is
set up to do is the following:
1. Test for "CEG_HEADER". If it exists, do something and move to the next
line of the text file.
2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME"
exists, do something and move to the next line of the text file.
3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc.
....
If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR",
"SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----".

Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"]
isn't there then in the worksheet I need to place 'N/A'," as noted in your
first post. I say this because, as noted above, if "PAYDETAILS" does NOT
exist (meaning that none of the other words exist either and the code has
reached the "Case Else" section), then insert the "----".

Are you familiar with the Debugging tools inside VBE? If not, then at least
use the following to test and evaluate your code:
In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow
you to evaluate your code one line at a time. As you evaluate your code
(i.e. press the F8 key) you will see a yellow line highlight syntax. As you
press F8, you will see the yellow line advance one line at a time. I think
that doing this simple exercise will help you see how your logic is behaving
and will hopefully help you see where/why the logic is not behaving as you
anticipated. Otherwise, you'll have to really spell out what you are looking
for.

For example, I don't know what you mean or intend when you say "...the
information retreived made only 4 rows on the worksheet, [but] columns L, or
M, or N used up 57 rows." Do you mean that columns L, or M, or N should have
used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2
rows only instead of spread out and separated by "----"?). How is the data
supposed to look? (For example, assuming from your post that the dollar
column is "A" and the subsequent columns move in alphabetical order [i.e. A1
= $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 =
----, etc. ] there should be the four rows of data (A1:C4) as shown in the
post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 =
$101.59; F3:G4 = ----).

Best,

Matt

Sub ReadText()

Dim strTextLine As String
Dim strFilename As String
Dim strNewFilepath As String
Dim intFileHandle As Integer
Dim Wks As Worksheet
Dim lngNextRow As Long

'should this file have a ".txt" extension? If so, then
' you'll have to adjust the suggestion for strNewFilepath
' listed below
strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570"

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

intFileHandle = FreeFile

'consider the following instead, which finds the first space,
' working right to left, in order to get the "new" filename:
'strNewFilepath = Right(strFilename, Len(strFilename) -
InStrRev(strFilename, " "))
strNewFilepath = Right(strFilename, 31)

Open strFilename For Input As intFileHandle

Set Wks = ThisWorkbook.Worksheets("ECI File Index")

With Wks
Do While Not EOF(intFileHandle)
Click to expand...
Click to expand...
Click to expand...
 
Hi Matthew

Your help has been much appreciate and I have learnt quite alot.

Thank you once again
Frederic
 
Back
Top