Recently we have got a requirement for executing a macro from command line. We have achieved the objective using following code snippet.

Step 1: Command line extractor module

Open the excel workbook and navigate to the Visual Basic editor. Add a new Module to the VBA project. Insert the following code snippet inside the module.

Option Explicit

‘ declare the kernel32 functions to extract the commandline arguments

‘GetCommandLineW returns the command line argument in number format

Declare Function GetCommandLine Lib “kernel32” Alias “GetCommandLineW” () As Long

‘This function is for getting the length of the commandline arguments

Declare Function lstrlenW Lib “kernel32” (ByVal lpString As Long) As Long

‘RtlMoveMemory will copy the specified data to the buffer

Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” (MyDest As Any, MySource As Any, ByVal MySize As Long)

‘Convert the command line from number format to string format

Public Function CmdToStr(Cmd As Long) As String

Dim Buffer() As Byte, StrLen As Long

If Cmd Then

   StrLen = lstrlenW(Cmd) * 2

   If StrLen Then

      ReDim Buffer(0 To (StrLen – 1)) As Byte

      CopyMemory Buffer(0), ByVal Cmd, StrLen

      CmdToStr = Buffer

   End If

End If

End Function

Step 2:  Workbook Open function

Handle the command line arguments inside the Workbook_Open() method, which will get executed when the excel opens the workbook.

Extract the command line with the help of the above module and get the macro name using the divider defined in the BAT file. Here, we used the ‘/a’ parameter as a divider. If there is a macro name passed in proper format, execute the same using Run command. Once the macro execution completes, wait for 5secs then save and close the excel process.

Option Explicit

Private Sub Workbook_Open()

    Dim CmdRaw As Long

    Dim CmdLine As String

    Dim Args As String

    Dim ArgArray As Variant   

    CmdRaw = GetCommandLine

    CmdLine = CmdToStr(CmdRaw)

    Args = “”

    If InStr(CmdLine, “/a”) > 0 Then

        Args = Mid(CmdLine, InStr(1, CmdLine, ” /a”) + 4)

        If Args <> “” Then

            Run Trim(Args)

            Application.OnTime Now + TimeValue(“00:00:05”), “ThisWorkbook.Save_Exit”

        End If

    End If

End Sub

Sub Save_Exit()

Application.Quit

ThisWorkbook.Close Saved = True

End Sub

Step3: Create the macro

Create the excel macro [TestMacro] and save the excel file.

Step4: BAT File

Create a BAT file to execute the macro from command line. Pass the name of the macro for execution in the below specified format.

 “C:\Program Files\Microsoft Office\Office12\EXCEL.EXE” “D:\MTest\MacroTest.xlsm” /a ThisWorkbook.TestMacro

Format:

1.       Specify the path to Excel exe
2.       Targeted xlsm file with Macro
3.        /a as a divider and
4.       macro name.

Step5: Execute the Bat file

Double click the bat file to execute the macro.

Update

Attaching sample Excel file and bat file to execute the same. Download the attachment, remove the extension “.doc” to get the zip file. Zip file contains both macro enabled excel file and bat file. Copy both the files into same directory and execute the bat file.

CommandLineSample.zip