Way to run Excel macros from command line or batch file?

You can launch Excel, open the workbook and run the macro from a VBScript file.

Copy the code below into Notepad.

Update the 'MyWorkbook.xls' and 'MyMacro' parameters.

Save it with a vbs extension and run it.

Option ExplicitOn Error Resume NextExcelMacroExampleSub ExcelMacroExample()   Dim xlApp   Dim xlBook   Set xlApp = CreateObject("Excel.Application")   Set xlBook = xlApp.Workbooks.Open("C:\MyWorkbook.xls", 0, True)   xlApp.Run "MyMacro"  xlApp.Quit   Set xlBook = Nothing   Set xlApp = Nothing End Sub 

The key line that runs the macro is:

xlApp.Run "MyMacro"

The simplest way to do it is to:

1) Start Excel from your batch file to open the workbook containing your macro:

EXCEL.EXE /e "c:\YourWorkbook.xls"

2) Call your macro from the workbook's Workbook_Open event, such as:

Private Sub Workbook_Open()    Call MyMacro1          ' Call your macro    ActiveWorkbook.Save    ' Save the current workbook, bypassing the prompt    Application.Quit       ' Quit ExcelEnd Sub

This will now return the control to your batch file to do other processing.

The method shown below allows to run defined Excel macro from batch file, it uses environment variable to pass macro name from batch to Excel.

Put this code to the batch file (use your paths to EXCEL.EXE and to the workbook):

Set MacroName=MyMacro"C:\Program Files\Microsoft Office\Office15\EXCEL.EXE" "C:\MyWorkbook.xlsm"

Put this code to Excel VBA ThisWorkBook Object:

Private Sub Workbook_Open()    Dim strMacroName As String    strMacroName = CreateObject("WScript.Shell").Environment("process").Item("MacroName")    If strMacroName <> "" Then Run strMacroNameEnd Sub

And put your code to Excel VBA Module, like as follows:

Sub MyMacro()    MsgBox "MyMacro is running..."End Sub

Launch the batch file and get the result:

For the case when you don't intend to run any macro just put empty value Set MacroName= to the batch.