从另一个工作簿运行宏 [英] Run a Macro from Another Workbook

问题描述

我在工作簿A中有一个宏，该宏在工作簿B中调用了一个宏.我希望运行工作簿B中的宏，然后我想关闭工作簿B.我不断收到一条错误消息，指出找不到所需的宏可以从工作簿B运行.我在这方面是个新手，但是我进行了非常彻底的搜索，无法独自提出任何建议.这是我的全部代码.

I have a macro in workbook A that calls a macro in workbook B. I want the macro in workbook B to run and then I want to close workbook B. I keep getting an error saying the macro cannot be found that I want to run from workbook B. I am pretty much a novice at this, but I have done a pretty thorough search and haven't been able to come up with anything on my own. Here is my code in it's entirety.

Public Sub InputDept()


Dim Cap As Workbook
Dim Cap2 As String

On Error Resume Next
Set Cap = Workbooks("NGD Source File for Net Budget Reporting.xlsx")
Cap2 = Cap.Name
On Error GoTo 0

Dim wb As Workbook
Dim Cap1 As Variant

Application.ScreenUpdating = False
If Cap Is Nothing Then
Cap1 = Application.GetOpenFilename("Excel Files(*.xl*)," & "*.xl*", 1)
    If Cap1 = False Then
    Exit Sub
    End If
Set wb = Workbooks.Open(Cap1)
Cap2 = ActiveWorkbook.Name
Else
Workbooks(Cap2).Activate
End If


Sheets("Dept Summary").Activate


Cells.Find(What:="Direct", after:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(1, 0).Select

Range(Selection, Selection.End(xlDown)).Select

Dim cRng As Range
Dim dRng As Range

Set dRng = Selection

For Each cRng In dRng
If cRng.Interior.ThemeColor = xlThemeColorAccent3 Then


    Dim mCalc As String
    Dim mSum As Workbook

    On Error Resume Next
    Set mSum = Workbooks("Master Calc with Macro.xlsm")
    mCalc = mSum.Name
    On Error GoTo 0

    Application.ScreenUpdating = False
    If mSum Is Nothing Then
        mSum1 = Application.GetOpenFilename("Excel Files.xl*),"& "*.xl*", 1)
    If mSum1 = False Then
        Exit Sub
    End If
        Set wb1 = Workbooks.Open(mSum1)
        mCalc = ActiveWorkbook.Name
    Else
        Workbooks(mCalc).Activate
    End If

    cRng.Copy

    Workbooks(mCalc).Activate
    Sheets("Data").Select
    Range("A5").Select

    Selection.PasteSpecial Paste:=xlPasteValues
    Sheets("Report").Activate

    Workbooks(mCalc).Application.Run ("!SummarizeMaster")

    Sheets("Report").Select
    ActiveSheet.Copy
    Cells.Select
    Cells.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.SaveAs _
        Filename:=Application.ThisWorkbook.Path & "\" & Format(Date -        28, "MMM") & " Files\" & Left(cRng, 7) & ".xlsx"

    ActiveWorkbook.Close

    Workbooks(mCalc).Close savechanges:=False

End If
Next cRng



End Sub

推荐答案

此行:

Workbooks(mCalc).Application.Run ("!SummarizeMaster")

需要稍作更改.即使看起来您要使用Workbooks(mCalc):

needs to be changed a little. You need to include the name of the workbook inside a single quotes, even if it looks like you are specifying the proper workbook with Workbooks(mCalc):

Workbooks(mCalc).Application.Run ("'Master Calc with Macro.xlsm'!SummarizeMaster")

您实际上可以将其缩短为:

You can actually just shorten it to:

Application.Run ("'Master Calc with Macro.xlsm'!SummarizeMaster")

这篇关于从另一个工作簿运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！