vba 如何从命令行启动 Excel 宏（没有 Worksheet_Open 事件）？

How to Launch an Excel macro from command line (Without Worksheet_Open Event)?

提问by M3HD1

Is it possible to launch an Excel Macro from command line?

是否可以从命令行启动 Excel 宏？

I don't want to use the Worksheet_Openevent and just open the Excel File.

我不想使用该Worksheet_Open事件而只是打开 Excel 文件。

I need to launch specific macro that exists in the Excel WorkBook.

我需要启动 Excel 工作簿中存在的特定宏。

采纳答案by M3HD1

I finally created a VB Script and launched it from the command line:

我终于创建了一个 VB 脚本并从命令行启动它：

Option Explicit

    LaunchMacro

    Sub LaunchMacro() 
      Dim xl
      Dim xlBook      
      Dim sCurPath

      sCurPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sCurPath & "\MyWorkBook.xlsm", 0, True)        
      xl.Application.Visible = True
      xl.Application.run "MyWorkBook.xlsm!MyModule.MyMacro"
      xl.DisplayAlerts = False      
      xlBook.saved = True
      xl.activewindow.close
      xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing

    End Sub 

回答by Govert

Use the Windows PowerShell, it has excellent COM interop support.

使用 Windows PowerShell，它具有出色的 COM 互操作支持。

I have the workbook c:\TestBeep.xlsm with a macro called "Test". This is my transcript:

我有工作簿 c:\TestBeep.xlsm 和一个名为“Test”的宏。这是我的成绩单：

PS C:\> $app = New-Object -comobject Excel.Application
PS C:\> $wb = $app.Workbooks.Open("c:\TestBeep.xlsm")
PS C:\> $wb.Name
TestBeep.xlsm
PS C:\> $app.Run("Test")
PS C:\> $app.Quit()

Optionally you can add in $app.Visible = $Trueto make the window visible.

（可选）您可以添加$app.Visible = $True以使窗口可见。

回答by t3dodson

If you would prefer to code in C# use this template

如果您更喜欢在 C# 中编码，请使用此模板

void Main()
{
    var oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
    try{
        //oExcelApp.Visible = true;
        var WB = oExcelApp.ActiveWorkbook;
        var WS = (Worksheet)WB.ActiveSheet;
        ((string)((Range)WS.Cells[1,1]).Value).Dump("Cell Value");
        oExcelApp.Run("test").Dump("macro");
    }
    finally{
        if(oExcelApp != null)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
        oExcelApp = null;
   }
}

回答by Eats

Thank you! Mehdi your answer worked for me too with a small adjustment. The line xl.Application.Visible = Truewas leaving open a zombie/phantom EXCEL.EXE process using up memory (found it through Windows Task Manager). Using xl.Application.Visible = Falseinstead seems to eliminate the zombie.

谢谢！Mehdi 您的回答也对我有用，只需稍作调整。该线路 xl.Application.Visible = True正在打开一个僵尸/幻影 EXCEL.EXE 进程，占用内存（通过 Windows 任务管理器找到它）。xl.Application.Visible = False相反使用似乎可以消除僵尸。

回答by sigil

AutoItalso offers great COM support and has a lot of built-in Excel-controlling functions. You can compile the script to an .EXE and then run it from the command line.

AutoIt还提供强大的 COM 支持，并具有许多内置的 Excel 控制功能。您可以将脚本编译为 .EXE，然后从命令行运行它。