Error Handling

[rsphorler]

Always have problems with the error handlers in my macros.

The macro below opens a file then does some other stuff to it but the
error handler does not seem to work and i still get an error if the
file is missing (error 1004 to be precise)

'opens the adjustments file
filepath = ThisWorkbook.Path & "\adjustments.xls"
On Error GoTo nofile1 'specifc error
handler
Workbooks.Open Filename:=filepath 'cause of potential error
On Error GoTo 0 'reset default
error handler
GoTo contadj2

'missing adjustments.xls error handler
nofile1:
On Error GoTo 0
adjust = MsgBox("The Adjustments file is missing, Do you wish to
continue without incorporating any adjustments?", vbYesNo)
If adjust = 6 Then
GoTo contadj2
Else
GoTo abortend
End If

contadj2:
REST OF MACRO

abortend:
end sub

Any ideas why it does not work, or many of the other handlers which
are similar in nature?

Thanks in advance

Richard

 

[Barb Reinhardt]

How about doing something like this

Dim oWB as workbook

Set oWB = nothing
on error resume next
Set oWB = Workbooks.Open Filename:=filepath
if not oWB is nothing then
'Put the code here you want to run if the workbook opens successfully.
end if

HTH,
Barb Reinhardt

 

[JLGWhiz]

On Error GoTo nofile1: would sent it to flag nofile1:

 

[JLGWhiz]

P.S. Same with contadj2 and abortend, you need the ":" to make it match.

 

[Chip Pearson]

When dealing with error handling, you must understand that VBA code operates
in two "modes". "Regular" mode is the normal mode. When an error occurs in
"regular" mode and any error handling other than On Error Resume Next or On
Error Goto 0 is in effect, VBA starts operating in "error" mode. In this
mode, no other error handling can take place. On Error statements will NOT
handle errors when VBA is already in "error" mode. You MUST revert to
"regular" mode after an error occurs by calling either the Resume or Resume
Next statement or exiting the procedure. Anything else will keep the code
running in "error mode" and no further error handling can take place.

See http://www.cpearson.com/excel/ErrorHandling.htm for a detail discussion
of this.


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

 

[Dave Peterson]

I like this way, too.

Just a typo:
Set oWB = Workbooks.Open Filename:=filepath
should be
Set oWB = Workbooks.Open(Filename:=filepath)
(with ()'s)