Workbook_Open Event
Workbook_Open Event
(OP)
I am opening an Excel Workbook via VBA and the workbook I am opening has Workbook_Open Event. How do I bypass the Workbook_Open event in code when opening the workbook in code?
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
|
RE: Workbook_Open Event
CODE
Dim Wkb As Workbook
Dim Path as String
Path = ThisWorkbook.Path & "\" 'for example
Set Wkb = Workbooks.Open(Path & "Workbook_To_Open.xls")
Wkb.RunAutoMacros xlAutoOpen
'...
Set Wkb = Nothing
End Sub
p.s. Auto_Open must be in a standard code module.
Regards,
Mike
RE: Workbook_Open Event
RE: Workbook_Open Event
The problem as I understand it: Workbook #1 contains code in its Workbook_Open event handler that should run when this workbook is opened from the Excel interface but not when it is opened via code in Workbook #2.
My recommendation solves that issue because unlike the Workbook_Open event handler, the special module procedure Auto_Open (a throwback to versions of Excel prior to 97) will not automatically fire in a workbook opened via code.
However, you got me thinking. There is another, perhaps better, way to achieve the same thing. Assume the original setup (code or procedure calls in the Workbook_Open event handler of Workbook #1). Here is a revised version of my previous procedure:
CODE
Dim Wkb As Workbook
Dim Path as String
On Error Resume Next
Path = ThisWorkbook.Path & "\" 'for example
Application.EnableEvents = False
Set Wkb = Workbooks.Open(Path & "Workbook_To_Open.xls")
Application.EnableEvents = True
'...
Set Wkb = Nothing
End Sub
Note the error handling. If this procedure errors without setting EnableEvents back to True, no other event handlers will fire.
Regards,
Mike