I need help with error handling 1

[sglab]

Hello everyone,

I need your help with error handling. I use On Error GoTo statement before opening workbook. It does catch the first occurrence of error '1004' - Method Open failed, it does go to error handler, but that's it. When next workbooks fails to open, application crashes with Visual Basic run-time error and I only have 2 options: End and Debug. So, error handling is obviously not working.
How should I handle this situation?
Below is a piece of code that I'm using:

On Error GoTo err_handle
Set wbk = xsl.Workbooks.Open(casePath & docPath, 0, True)

-------------

err_handle:
 if Err.Number = 1004 then
  err.clear
  GoTo continue_exec
 end if

Thank you in advance.

 

[xlbo]

and what do you WANT to happen if the workbook does not exist ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[sglab]

Hi Geoff,

Thanks for reply.
Well, it's not the case where workbook doesn't exists. It does. Along with many others which my application processes in a loop. I was able to track down many problems that could occur during batch process and one of them - password-protected workbooks. I don't want to stop execution, but log these in the log file and continue with next workbook.
But I think I already fixed the problem: instead of using On Error GoTo, I use On Error Resume Next and after error-causing line I check for Err.Number and if it's the one I'm looking for - 1004 - then I log the error, clear Err object and continue execution.

If there still is other, more efficient approach, I'd gladly take a look at it.

Thanks.
Sergey.

 

[xlbo]

nope - your described approach there is what I would've suggested given your description of what you need to do.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[sglab]

Thanks, Geoff.

Have a great day.

Sergey.

 

[PHV]

Replace this:
GoTo continue_exec
By this:
Resume continue_exec

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sglab]

Hi PH,

Just one question. Using of Resume instead of GoTo is a better programming practice or your preference?

Thank you.
Sergey.

 

[PHV]

This is THE way for an error handler routine.
Without the Resume instruction your code is still running as managing the first trapped error and thus bomb out on the next error.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sglab]

I got you, but in fact - since I knew all the password-protected workbooks before execution - error handler worked fine even with GoTo statement and trapped all these occurrences. Here's the place in the code:

Set wbk = xsl.Workbooks.Open(casePath & docPath, 0, True)
  If Err.Number = 1004 Then
   Err.Clear
   GoTo continue_exec
  End If

So why do you think it worked?

Thanks a lot.

 

[xlbo]

PHV - is that the case ?? I always thought that "on error resume next" did just that - until there is an "on error goto 0" line, it will just keep going regardless of errors

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[PHV]

No On Error Resume Next above this code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

instead of using On Error GoTo, I use On Error Resume Next and after error-causing line I check for Err.Number

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[sglab]

Oh, I'm sorry. There is. I forgot to copy it over.

 

[PHV]

Geoff, my 1st answer was for the 1st post.

 

[xlbo]

aaaaaaaaaaaaaaaaahhhh. Thanks for the clarification.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question