CODEWITHSUNDEEP
excelvba
Hsiao-I Yang
Hsiao-I Yang

Reputation: 65

Run excel macro from batch file

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?

Upvotes: 0

Views: 5598

Answers (1)

Konrad
Konrad

Reputation: 51

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"

Upvotes: 2

Related Questions