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

Question

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

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

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

Answer 1

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

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

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 = $True to make the window visible.

Answer 2

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

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