CODEWITHSUNDEEP
vbaexcelevents
Soulfire
Soulfire

Reputation: 4296

How can I call Workbook_Open on Workbook A from Workbook B?

My situation is that I am trying to open Workbook A from Workbook B and run the Workbook_Open subroutine. The issue with just simply using Workbooks.Open("c:\myworkbook.xls") is that I need to plant data in Workbook A before the Workbook_Open subroutine runs.

I have two ideas, neither of which I particularly like, so I was wondering if there was a better way. My two ideas are as follows.

Option 1: Open Workbook A with macros disabled, plant the data, save and close the workbook then re-open it with macros enabled. The reasons I would prefer not to do this is: I am dealing with a .xls file that has a fair amount of formatting (basically I am simulating how a human would use it thousands of times over) and saving the file thousands of times may cause the file to corrupt. Additionally this takes a fair amount of time to open the workbook twice, and it does not seem at all efficient to me. One of the big points of emphasis is speed and efficiency with the quickest turnaround time.

Option 2: Duplicate the Workbook_Open code as a public subroutine in elsewhere within a module. This is the more desirable of the two, but the issue is I do not necessarily have permission to perform this action and doing this will involve plenty of red tape, red flags, etc.

Is there any way to do something like this:

Workbooks("Workbook A").Application.Run (Workbooks("Workbook A").Workbook_Open)

Upvotes: 3

Views: 3990

Answers (2)

Tim Williams
Tim Williams

Reputation: 166331

From workbook B:

'add vb reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
Sub Tester()

    Dim wb As Workbook
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long

    Set wb = Workbooks("BookA.xlsm")
    Set VBProj = wb.VBProject
    Set VBComp = VBProj.VBComponents("ThisWorkbook")
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, "Public Sub Blah()"
        LineNum = LineNum + 1
        .InsertLines LineNum, "   Workbook_Open"
        LineNum = LineNum + 1
        .InsertLines LineNum, "End Sub"
    End With

    Application.Run "BookA.xlsm!ThisWorkbook.Blah"

End Sub

See:http://www.cpearson.com/excel/vbe.aspx

Upvotes: 4

Degustaf
Degustaf

Reputation: 2670

Workbook_Open is a private sub. You cannot call it directly.

May I offer Option 3: Copy the code from Workbook_open into a sub in Workbook B. Then you can call it will be public and you can call it for your simulation.

In addition, if corruption is a concern, create a copy of Workbook A to use for your tests. Then if it gets corrupted, the original will still be available.

Upvotes: 1

Related Questions