Way to run Excel macros from command line or batch

Question

I have an Excel VBA macro which I need to run when accessing the file from a batch file, but not every time I open it (hence not using the open file event). Is there a way to run the macro from the command line or batch file? I'm not familiar with such a command.

Assume a Windows NT environment.

Answer 1

you could write a vbscript to create an instance of excel via the createobject() method, then open the workbook and run the macro. You could either call the vbscript directly, or call the vbscript from a batch file.

Here is a resource I just stumbled accross: http://www.codeguru.com/forum/showthread.php?t=376401

Answer 2

Instead of directly comparing the strings (VB won't find them equal since GetEnvironmentVariable returns a string of length 255) write this:

Private Sub Workbook_Open()     
    If InStr(1, GetEnvironmentVariable("InBatch"), "TRUE", vbTextCompare) Then
        Debug.Print "Batch"  
        Call Macro
    Else    
        Debug.Print "Normal"     
    End If 

End Sub 

Answer 3

I have always tested the number of open workbooks in Workbook_Open(). If it is 1, then the workbook was opened by the command line (or the user closed all the workbooks, then opened this one).

If Workbooks.Count = 1 Then

    ' execute the macro or call another procedure - I always do the latter  
    PublishReport

    ThisWorkbook.Save

    Application.Quit

End If

Answer 4

I'm partial to C#. I ran the following using linqpad. But it could just as easily be compiled with csc and ran through the called from the command line.

Don't forget to add excel packages to namespace.

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

Answer 5

You can check if Excel is already open. There is no need to create another isntance

   If CheckAppOpen("excel.application")  Then
           'MsgBox "App Loaded"
            Set xlApp = GetObject(, "excel.Application")   
   Else
            ' MsgBox "App Not Loaded"
            Set  wrdApp = CreateObject(,"excel.Application")   
   End If

Answer 6

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 strMacroName
End 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.