'On Error' within 'For' loop
 

I am using VB to query a group of text files for buzzwords contained within
them. But to simplify my situation I will say I am only interested in
determining the modified date of each file. I have attempted to use an error
handler within a for loop.

The plan:
A random list of file names are in Column A. Column B will list the
modified date for each. If the file is not found, "Error reading file" is
listed in column B instead.

The reality:
I receive a run-time error 53 if there are two or more invalid filenames
simultaneously. I have read through the VB help for 'On Error Statement' and
understand that the error handler cannot handle an error until the previous
error is 'handled'. So my question is, "Is there a way to clear the handler
and allow the next loop to continue?" Or is there an alternate way to reach
the same thing like ignoring the error? A portion of my program is below.
Thanks in advance.


For Blank = 1 To 10
On Error GoTo Line6
fName = Cells(Blank, "A")
Cells(Blank, "B").Value = FileDateTime(fName)
GoTo Line8
Line6: Cells(Blank, "B").Value = "ERROR ACCESSING LOGFILE"
Resume Next
Line8: Next Blank

'On Error' within 'For' loop
 

You're code will be MUCH cleaner and easier to understand and maintain if
you get rid of the Gotos. Test the error condition in the main logic of your
procedure, rather jumping around with Gotos.

On Error Resume Next
For Blank = 1 To 10
FName = Cells(Blank, "A").Value
Err.Clear
Cells(Blank, "B").Value = FileDateTime(FName)
If Err.Number < 0 Then
Cells(Blank, "B").Value = "Error Accessing Log File"
Err.Clear
End If
Next Blank


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"flaterp" wrote in message
...
I am using VB to query a group of text files for buzzwords contained within
them. But to simplify my situation I will say I am only interested in
determining the modified date of each file. I have attempted to use an
error
handler within a for loop.

The plan:
A random list of file names are in Column A. Column B will list the
modified date for each. If the file is not found, "Error reading file" is
listed in column B instead.

The reality:
I receive a run-time error 53 if there are two or more invalid filenames
simultaneously. I have read through the VB help for 'On Error Statement'
and
understand that the error handler cannot handle an error until the
previous
error is 'handled'. So my question is, "Is there a way to clear the
handler
and allow the next loop to continue?" Or is there an alternate way to
reach
the same thing like ignoring the error? A portion of my program is below.
Thanks in advance.


For Blank = 1 To 10
On Error GoTo Line6
fName = Cells(Blank, "A")
Cells(Blank, "B").Value = FileDateTime(fName)
GoTo Line8
Line6: Cells(Blank, "B").Value = "ERROR ACCESSING LOGFILE"
Resume Next
Line8: Next Blank

'On Error' within 'For' loop
 

Try to test the for the file's existence first. That way, you don't generate
any error. Also, you can use the 'On Error' for real errors.

On error goto errhandler
For Blank = 1 To 10
fName = Cells(Blank, "A")
If Dir(fName) = "" Then
Cells(Blank, "B").Value = "ERROR ACCESSING LOGFILE"
Else
Cells(Blank, "B").Value = FileDateTime(fName)
End If
Next Blank
Exit Sub
errhandler:
Msgbox "Unexpected error!"






"flaterp" wrote:

I am using VB to query a group of text files for buzzwords contained within
them. But to simplify my situation I will say I am only interested in
determining the modified date of each file. I have attempted to use an error
handler within a for loop.

The plan:
A random list of file names are in Column A. Column B will list the
modified date for each. If the file is not found, "Error reading file" is
listed in column B instead.

The reality:
I receive a run-time error 53 if there are two or more invalid filenames
simultaneously. I have read through the VB help for 'On Error Statement' and
understand that the error handler cannot handle an error until the previous
error is 'handled'. So my question is, "Is there a way to clear the handler
and allow the next loop to continue?" Or is there an alternate way to reach
the same thing like ignoring the error? A portion of my program is below.
Thanks in advance.


For Blank = 1 To 10
On Error GoTo Line6
fName = Cells(Blank, "A")
Cells(Blank, "B").Value = FileDateTime(fName)
GoTo Line8
Line6: Cells(Blank, "B").Value = "ERROR ACCESSING LOGFILE"
Resume Next
Line8: Next Blank