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:
- 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.
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.
Hello: Thanks for the pointers here. I am a novice however and do not understand if there is just one module and the two sections of code both go into it? I did build it that way but when i run the .bat the excel workbook opens but the macro doesn’t run. Thanks!
Yes, you can add the code snippets to the same module.
I will try to attach the sample excel to the post by next week.
Thanks
Ambily
Yes, that would be great! I haven’t forgotten my project, hope you can find some time to squeeze my request in. Thanks
Hi Ambily,
Wondering if you could find the time to post the sample Excel with your code all in one module?
Thanks!
Hi Kristi
I have updated the post with sample excel and corresponding bat files. If you face any issues, please let me know, I can share the same through mail.
Thanks
Ambily
Thank you very much for getting back to this – I really appreciate your time and efforts. I will work on testing this for my needs as soon as I can! Thanks again!
Hi Ambily,
I have written a macro and I’m able to execute the macro from command line.
Is it possible that I can pass a parameter to macro from command line?
Thanks
Hi Krishna
The sample provided and the details are explaining the same only; how to pass a parameter from command line.
Thanks
Ambily