Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - 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)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...