Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
'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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
'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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
'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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do while loop error.... | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Problem with 'For' loop execution [VBA,Excel XP] | Excel Programming | |||
loop error | Excel Programming | |||
loop error | Excel Programming |