Hi Matthew

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

Thank you once again
Frederic

"Matthew Herbert" wrote:

Frederic,

You are very right about changing strTextLine to strArr(lngCnt). (I
definitely missed that for coding on the fly). An alternative method would
be to place strTextLine = strArr(lngCnt) right after the For lngCnt =
UBound... statement. This would make it so that you wouldn't have to do a
find/replace for all of the strTextLine variables within the Case statements.
(See the revised code below).

As for the "new" code block from the previous post, you can actually place
it outside the Select statement (see the revised code below). In the code
below, I moved the "new" code block from the "Case "SPRCONTBTN"" to be
located after the "End Select" statement. What this will do is evaluate the
Case statement and regardless of how the Case statement evaluates, the "new"
code block will be evaluated with every item in the array (or each of the
lines in the text file). I think this is what you are looking to do. If
this isn't what you want to do, then you'll have to copy/paste the "new" code
block into each of the Case statements and adjust the code to fit that
particular Case statement.

Again, debug with F8 to follow the logic of the new code (because I can't
tell if it's behaving correctly without seeing the text file, and without
being in your shoes). If you still have questions, keep posting detailed
questions.

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)

strTextLine = strArr(lngCnt)

'evaluate the first 10 characters of each item
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
'you need to decide if you need to add code in here
End Select

'----------------------------------------------------
'NEW CODE BLOCK FOR TESTING THE "NEXT" LINE FOR
' PAYDETAILS.
'This will be evaluated independent of the Case statement

'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
'----------------------------------------------------
Next lngCnt
End With

End Sub


"bluewatermist" wrote:

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" wrote:

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)