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)
