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.
‘ 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
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.
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
Application.OnTime Now + TimeValue(“00:00:05”), “ThisWorkbook.Save_Exit”
ThisWorkbook.Close Saved = True
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
- Specify the path to Excel exe
- Targeted xlsm file with Macro
- /a as a divider and
- macro name.
Step5: Execute the Bat file
Double click the bat file to execute the macro.
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.