Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
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 "rsphorler" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
On Error GoTo nofile1: would sent it to flag nofile1:
On Error GoTo nofile1 is a mismatch and it will be ignored. "rsphorler" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
P.S. Same with contadj2 and abortend, you need the ":" to make it match.
"rsphorler" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
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) "rsphorler" wrote in message ups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
I like this way, too.
Just a typo: Set oWB = Workbooks.Open Filename:=filepath should be Set oWB = Workbooks.Open(Filename:=filepath) (with ()'s) Barb Reinhardt wrote: 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 "rsphorler" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
error handling | Excel Programming | |||
Error Handling | Excel Programming | |||
Error handling | Excel Programming |