How can I call Workbook_Open in Book A of Book from Book B?
My situation is that I am trying to open book A from book B and run the Workbook_Open subroutine. The problem with just using it Workbooks.Open("c:\myworkbook.xls")
is that I need to set the data in book A before the subroutine Workbook_Open
runs.
I have two ideas that I don't particularly like, so I was wondering if there is a better way. My two ideas are as follows.
Option 1: Open workbook A with macros disabled, set data, save and close workbook, and then reopen it with macros enabled. The reasons I would rather not do this: I am dealing with a .xls file that has a fair amount of formatting (basically I simulate how a human would use it thousands of times) and saving the file thousands of times could cause corruption file. Also, it takes a long time to open the book twice and it doesn't seem to me to be effective at all. One of the main focus points is speed and efficiency with the fastest turning times.
Option 2: Duplicate the Workbook_Open code as a public subroutine elsewhere within the module. This is the more desirable of the two, but the problem is that I don't necessarily have permission to do this, and it will take a lot of red tape, red flags, etc.
Is there a way to do something like this:
Workbooks("Workbook A").Application.Run (Workbooks("Workbook A").Workbook_Open)
source to share
Workbook_Open
is private. You cannot call it directly.
May I suggest option 3: Copy the code from Workbook_Open
into a subroutine in book B. Then you can call it, it will be public, and you can call it to simulate.
Also, if corruption is a concern, please make a copy of Book A to use for your tests. Then, if it gets damaged, the original will be available.
source to share
From Book 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: https://web.archive.org/web/www.cpearson.com/excel/vbe.aspx
source to share