vba 从另一个工作簿运行宏

Run a Macro from Another Workbook

提问by JudeD

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.

我在工作簿 A 中有一个宏调用工作簿 B 中的宏。我希望工作簿 B 中的宏运行，然后我想关闭工作簿 B。我不断收到错误消息，说找不到我想要运行的宏工作簿 B。我在这方面几乎是新手，但我已经进行了非常彻底的搜索，但我自己没有想出任何东西。这是我的完整代码。

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

回答by Stewbob

This line:

这一行：

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

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)：

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")

回答by Sharunas Bielskis

If the macro you need to find relative macro path by using workbook path from which you run macro and you need to run several macros from the array list, the code below will help:

如果宏需要使用运行宏的工作簿路径来查找相对宏路径，并且需要从数组列表中运行多个宏，则以下代码将有所帮助：

Dim relativePath As String, programFileName As String
Dim selectedProgramsFiles() As String, programsArrayLastIndex As Byte, I As Byte

For I = 0 To programsArrayLastIndex 'Loop through all selected programs
    programFileName = selectedProgramsFiles(I)
    relativePath = ThisWorkbook.Path & "\" & programFileName
    Workbooks.Open Filename:=relativePath

    Application.Run ("'" & relativePath & "'!ModuleName.Main")

   Workbooks(programFileName).Activate
   ActiveWorkbook.Close SaveChanges:=False
Next I 'For I = 0 To programsArrayLastIndex 'Loop through all selected program

回答by TommyExcels

Application.Run "PERSONAL.xlsb!ClearYellow", 0

ClearYellowis the name of the sub in Personal.xlsbthat is being run. The "0" is the first argument of this sub (would omit if no arguments, could add more arguments separated by commas)

ClearYellow是Personal.xlsb正在运行的子程序的名称。“0”是这个子的第一个参数（如果没有参数，将省略，可以添加更多由逗号分隔的参数）

Applicationdoes not seem to be needed

Application似乎不需要

This could be used to run from some other workbook also; the workbook would have to be open; if the name of that workbook had a space in it, the name would have to be surrounded by ''

这也可以用于从其他工作簿运行；工作簿必须是开放的；如果该工作簿的名称中有空格，则名称必须用''

Calldoes not work cross workbooks; haven't tested within same workbook or within same module

Call不适用于跨工作簿；没有在同一个工作簿或同一个模块中测试过