Excel: workbooks.open doesn't work (not a newbie problem)

[tranner]

This one is small enough to try out on your own machine.


Book1 has this event procedure in the ThisWorkbook object:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Workbooks.Open "SomeWorkbook"
    MsgBox "foo"
End Sub

Book2 has this sub in a code module:

Sub test()
    Dim wb As Workbook
    Set wb = Workbooks.Open("Book1.xls")
    wb.PrintOut
End Sub

When I run test() in Book2, Book1 opens, msgboxes the 'foo' and prints, but the Workbooks.Open "SomeWorkbook" line doesn't do anything. Is there any way to make it work? Maybe the Workbooks.Open calls can't be nested, but I can't find any indication of this in documentation. Thanks for you help.

Will

[RobDog888]

Welcome to the Forums.

So your saying that the Workbooks.Open "SomeWorkbook" is not executing when opened and printed from book2.

[tranner]

Precisely. Now, if I open Book1 maually and print, the Workbooks.Open "SomeWorkbook" does work, and will throw an error is SomeWorkbook doesn't exist.

[RobDog888]

As a test, make sure ALL instances of Excel are closed and no instances are running in the taskmanager running processes list. Execute
your code and you should have showing 3 instances of Excel.exe.
It may be that its being shown as invisible if the last time it was saved it was hidden or invisible.

[westconn1]

as far as i can understand you have to do mare than just write a sub called beforeprint

Application events occur when a workbook is created or opened or when any sheet in any open workbook changes. To write event procedures for the Application object, you must create a new object using the WithEvents keyword in a class module. For more information, see Using Events with the Application Object.

Before you can use events with the Application object, you must create a new class module and declare an object of type Application with events. For example, assume that a new class module is created and called EventClassModule. The new class module contains the following code.

Public WithEvents App As ApplicationAfter the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)

Before the procedures will run, however, you must connect the declared object in the class module with the Application object. You can do this with the following code from any module.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End SubAfter you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur.

information above from vba helpfile

pete

[RobDog888]

He is using the default events behind each workbook so no WithEvents are required. Also, the _BeforePrint event is
executing, just not the Workbooks.Open(Blah).

[RobDog888]

Looks like there is a limitation of one workbook opening another from another via automation.
I was searching earlier on MS and I couldnt find anything that states this. I would suggest that instead of daisy-chaining the opening of
workbooks, you have a central workbook to open all the wbs you need.

[tranner]

I guess it's to prevent a recursive loop of opening workbooks. Weird how this isn't documented though. Oh well.

[RobDog888]

Thats what I was thinking and also that it may come from a security standpoint. Dont want to open a workbook and have it
open "other" malicious workbooks, etc. like some virus' and spam.