I have a program that I kick off out of Excel, which dumps results to a log
file. I'd like to parse the log file to see if everything worked OK. Of all
the lines that get dumped, only the bottom is important...

[lots of text]
03/30/2007 13:19:50 Loading is complete. Loading Status is as follows:
Records Read: 1241
Loaded: 1241
Processed: 1099
Failed to Load: 0
Failed to Process: 142
03/30/2007 13:19:50 Import Finished
[a little bit of text]

What I'd like to do is find the last instance of these lines, specifically
the Failed to Load and Failed to Process counts. That way after kicking off
the program, I can tell the user what happened.

So the first question is how to get the counts correctly. I can determine
whether the line has "Failed to Load:" using InStr, but how do I say "parse
out the number from the : to the end of the line"? This is something I'm not
familiar with in VBA.

The second question is whether or not I can loop backwards. Since this file
can grow very large I think performance might be an issue. If I work from the
bottom up, I can stop after processing something like a dozen lines.

Any suggestions?

Maury

Yes you can loop backwards, there are various methods, I will supply one. As
far as your number issue, adapt this: =RIGHT(A1,LEN(FIND(":",A1))+1) it will
return all numbers to the right of the :.

This will get you the last row in a particular column, I used A:
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

then use that number in your loop or for next something like
for I = lastrow to 1 step -1
next <-not tested
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Maury Markowitz" wrote:

I have a program that I kick off out of Excel, which dumps results to a log
file. I'd like to parse the log file to see if everything worked OK. Of all
the lines that get dumped, only the bottom is important...

[lots of text]
03/30/2007 13:19:50 Loading is complete. Loading Status is as follows:
Records Read: 1241
Loaded: 1241
Processed: 1099
Failed to Load: 0
Failed to Process: 142
03/30/2007 13:19:50 Import Finished
[a little bit of text]

What I'd like to do is find the last instance of these lines, specifically
the Failed to Load and Failed to Process counts. That way after kicking off
the program, I can tell the user what happened.

So the first question is how to get the counts correctly. I can determine
whether the line has "Failed to Load:" using InStr, but how do I say "parse
out the number from the : to the end of the line"? This is something I'm not
familiar with in VBA.

The second question is whether or not I can loop backwards. Since this file
can grow very large I think performance might be an issue. If I work from the
bottom up, I can stop after processing something like a dozen lines.

Any suggestions?

Maury

Since there's only a couple of items to extract, I'd just use a couple of if
statements:

Option Explicit
Sub testme()

Dim iRow As Long
Dim wks As Worksheet
Dim ColonPos As Long
Dim myStr As String

Set wks = ActiveSheet

Dim LoadFailedCount As Long
Dim ProcFailedCount As Long

LoadFailedCount = -99999
ProcFailedCount = -99999

With wks
For iRow = .Cells(.Rows.Count, "A").End(xlUp).Row To 1 Step -1

myStr = LCase(Trim(.Cells(iRow, "A").Value))

If myStr Like LCase("Failed to Load:") & "*" Then
ColonPos = InStr(1, myStr, ":", vbTextCompare)
LoadFailedCount = Val(Trim(Mid(myStr, ColonPos + 1)))
ElseIf myStr Like LCase("Failed to Process:") & "*" Then
ColonPos = InStr(1, myStr, ":", vbTextCompare)
ProcFailedCount = Val(Trim(Mid(myStr, ColonPos + 1)))
End If

If LoadFailedCount = 0 _
And ProcFailedCount = 0 Then
Exit For
End If

Next iRow
End With

MsgBox LoadFailedCount & vbLf & ProcFailedCount

End Sub



Maury Markowitz wrote:

I have a program that I kick off out of Excel, which dumps results to a log
file. I'd like to parse the log file to see if everything worked OK. Of all
the lines that get dumped, only the bottom is important...

[lots of text]
03/30/2007 13:19:50 Loading is complete. Loading Status is as follows:
Records Read: 1241
Loaded: 1241
Processed: 1099
Failed to Load: 0
Failed to Process: 142
03/30/2007 13:19:50 Import Finished
[a little bit of text]

What I'd like to do is find the last instance of these lines, specifically
the Failed to Load and Failed to Process counts. That way after kicking off
the program, I can tell the user what happened.

So the first question is how to get the counts correctly. I can determine
whether the line has "Failed to Load:" using InStr, but how do I say "parse
out the number from the : to the end of the line"? This is something I'm not
familiar with in VBA.

The second question is whether or not I can loop backwards. Since this file
can grow very large I think performance might be an issue. If I work from the
bottom up, I can stop after processing something like a dozen lines.

Any suggestions?

Maury

--

Dave Peterson

"John Bundy" wrote:

This will get you the last row in a particular column, I used A:
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

But this only works if the file fits into Excel and can be loaded up. In
this example the file is very often too large to be loaded because the number
of lines is 64k

But for testing purposes I just went ahead and did it the brute force way.
It takes well under 1 second! There's times when VBA does manage to impress
me, and this is one of those times.

Maury