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.

I suggest you use a [tag:vbs] instead. You can automate Excel via the script — brettdj, Jun 04 '12 at 13:34
I agree with brettdj. Use a vbs. If you want to run from the commandline then you will have to write a batch file and use the `Worksheet_Open` event. — Siddharth Rout, Jun 04 '12 at 13:44
Is it possible to make something like batch file using `VBS` ? I never used it, can you please give me an example to launch `MyMacro` in `MyExcelFile.xlsm` ? — M3HD1, Jun 04 '12 at 13:49
Hope this helps? http://stackoverflow.com/questions/2050505/way-to-run-excel-macros-from-command-line-or-batch-file — Siddharth Rout, Jun 04 '12 at 13:49
Also see this example http://stackoverflow.com/questions/10530299/macro-gets-disabled-if-called-using-a-vb-script — Siddharth Rout, Jun 04 '12 at 13:56
Is this not possible without VBS? I don't want to have to call a VBS Script via batch — FreeSoftwareServers, Jan 09 '20 at 22:58

score 14 · Answer 1 · answered Jun 04 '12 at 14:02

14

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.

answered Jun 04 '12 at 14:02

Govert

15,894
3
59
68

Thanks, but i'm not sure that the users have PowerShell installed ... I finally used a VB Script and launch it from command line – M3HD1 Jun 05 '12 at 08:43

M3HD1 · Accepted Answer · 2019-12-03T15:22:11.320

10

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

edited Dec 03 '19 at 15:22

answered Jun 05 '12 at 08:45

M3HD1

1,424
5
34
50

Consider accepting this so others know your issue was resolved. [answer] – JimmyPena Jun 05 '12 at 17:19
3

Yes, but since it was my own answer i should wait 24H :) – M3HD1 Jun 06 '12 at 08:27
Note that while you can set the `Saved` property, it does not save the file. Use the `Save` method instead. – Rachel Hettinger Aug 18 '14 at 19:13
Did you figure out how to generalize this for any code? – Lime Aug 21 '20 at 03:59
Nope @Lime I didn't – M3HD1 Sep 08 '20 at 08:54

score 0 · Answer 3 · answered Jun 04 '12 at 19:23

0

AutoIt also offers great COM support and has a lot of built-in Excel-controlling functions. You can compile the script to an .EXE and then run it from the command line.

answered Jun 04 '12 at 19:23

sigil

9,102
35
113
194

score 0 · Answer 4 · answered Dec 09 '14 at 20:55

0

If you would prefer to code in C# use this template

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

answered Dec 09 '14 at 20:55

t3dodson

3,734
2
31
38

It was unfortunately not possible to use C# ! The only possible option was Sheel Scripting ... Thanks anyway ! but i can't test your solution ... – M3HD1 Dec 10 '14 at 16:18
not a problem, I saw this question was old. I was looking for this functionality and I wanted to give a way to do it in C# for others who may find it useful in the future. – t3dodson Dec 11 '14 at 00:48

score 0 · Answer 5 · answered Sep 13 '17 at 00:52

Thank you! Mehdi your answer worked for me too with a small adjustment. The line xl.Application.Visible = True was leaving open a zombie/phantom EXCEL.EXE process using up memory (found it through Windows Task Manager). Using xl.Application.Visible = False instead seems to eliminate the zombie.

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

5 Answers5

Linked