If workbook not found terminate macro

Posted by Andonny on May 25, 2001 4:27 AM

Hi,
I am looking for a macro in Book1 which terminates the macro if it can't find the Book2 without warning. Usually if it can't find Book2 it is trying to look for it and a popup window appears where you have the possibility to cancel and then the macro continues as normal. I don't want that. I would like it to terminate without executing the rest of the macro.

This is the code I am using at the moment:

Range("D1").Select
ActiveCell.FormulaR1C1 = "=[Book2]Sheet1!R1C2-[Book2]Sheet1!R1C3"
Range("C2").Select

Thanks a million
Andonny

Posted by Dave Hawley on May 25, 2001 4:36 AM

Hi Andonny

This should do it!

Application.DisplayAlerts = False

Range("D1").FormulaR1C1 = "=[Book2]Sheet1!R1C2-[Book2]Sheet1!R1C3"

Set it back to true on completion though!

Dave
OzGrid Business Applications

Posted by Andonny on May 25, 2001 5:32 AM

Hi Dave,
Thank you very much for your recommendation. One thing perhaps. Is it actually possible to terminate the macro without executing the rest of the code if it can't find the Book2.
Application.DisplayAlerts = False
Range("D1").FormulaR1C1 = "=[Book2]Sheet1!R1C2-[Book2]Sheet1!R1C3"

Terminate here

Application.DisplayAlerts = False

Thank you very much
Andonny

Posted by Dave Hawley on May 25, 2001 6:00 AM

Ok, we need to go one step futher then. This code should quit on any error in cell D1, which is what it should be in the cell after not finding it's link. I would think ,most errors would parse zero to the variable "iErr"

Dim iErr As Integer
Application.DisplayAlerts = False
On Error Resume Next
Range("D1").FormulaR1C1 = "=[Book2]Sheet1!R1C2-[Book2]Sheet1!R1C3"
iErr = Range("D1").Find("#REF!", Range("D1")).Row
If iErr = 0 Then Exit Sub

Dave

OzGrid Business Applications

Posted by Andonny on May 25, 2001 6:26 AM

Hi again,
Sorry to bother you again. I tried as below and it terminates I think, but when it does not find the #REF! (like when the Book2 exists) then it does not continue the rest of the code. It should place a "1" into D2.

Sub Terminate()

Dim iErr As String
Application.DisplayAlerts = False
On Error Resume Next
Range("D1").FormulaR1C1 = "=[Book8_1.xls]Sheet1!R1C1-[Book8_1.xls]Sheet1!R1C2"
iErr = Range("D1").Find("#REF!", Range("D1")).Row
If iErr = "0" Then Exit Sub
Range("D2").Select
ActiveCell.FormulaR1C1 = "1"
End Sub

Thanks a million
Andonny

Posted by Dave Hawley on May 25, 2001 7:08 AM

Got my "=" mixed up with my "&LT;&GT;".
Sub Terminate()

Dim iErr As Integer
Application.DisplayAlerts = False
On Error Resume Next
Range("D1").FormulaR1C1 = "=[Book2.xls]Sheet1!R1C1-[Book2.xls]Sheet1!R1C2"
iErr = Range("D1").Find("#REF!", Range("D1")).Row
If iErr &LT;&GT; 0 Then Exit Sub
Range("D2").Select
ActiveCell.FormulaR1C1 = "1"
End Sub

Dave
OzGrid Business Applications