Run excel macro from batch file

Question

I have an Excel VBA macro, its function is to compare two excels and build the comparison result in a new excel file. When running macro, a window will pop up and let me choose the files to compare.

Now I want to do this from the command line. I created a batch file with the following content:

Set MacroName=RoundTrip_VCRIComparison3
"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" "C:\2_workitems_exported_macro_20200319.xlsm"

And 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 strMacroName
End Sub

But this can only open my excel and run macro, but it cannot specify files to compare. Does anyone know how to solve it?

Are you able to put that macro into the workbook you’re running workbook_open from? Then you could just call the macro as normal — Marcucciboy2
Can you modify the "compare files" macro to check for additional environmental variables (whose values are the filenames to compare)? — Tim Williams
Yes. I just want to change the manual operation step to batch mode. — Hsiao-I Yang
Modify the "Compare Files" macro? I do not understand what you mean. — Hsiao-I Yang
Your Workbook_Open already reads "MacroName" from an environmental variable. It can also read the names of the two files to be compared, and pass those names to the macro when you call it using Run — Tim Williams

Konrad Konrad · Accepted Answer · 2020-03-24T12:59:26

I use VBScripts to run marcro from batch:

Try to save following code in text editor with .VBS extension

  Dim args, objExcel

    Set args = wScript.Arguments
    Set objExcel = CreateObject("Excel.Application")

    objExcel.workbooks.Open args(0)
    objExcel.visible = False

    objExcel.Run "Filename.xlsm!ModuleName.MacroName"

    objExcel.Activeworkbook.Close(0)
    objExcel.Quit

then create batch file with following code:

cscript PreviousCreatedScriptName.vbs "E:\Main BE\FullPathToFileWithMacro.xlsm"

Remember to replace by your own:

"Filename.xlsm!ModuleName.MacroName"
PreviousCreatedScriptName.vbs "E:\Main BE\FullPathToFileWithMacro.xlsm"

Run excel macro from batch file

1 Answers