Command Line Execution of Excel Macro

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

Advertisements

About ambilykk

I am a Technology Evangelist on Microsoft Technologies. I am carrying the passion on Microsoft technologies specifically on web technologies such as ASP .Net and Ajax. My interests also include Azure and Visual Studio. Technology adoption and learning is my key strength and technology sharing is my passion.
This entry was posted in Office and tagged . Bookmark the permalink.

8 Responses to Command Line Execution of Excel Macro

  1. Kristi says:

    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!

  2. Kristi says:

    Hi Ambily,

    Wondering if you could find the time to post the sample Excel with your code all in one module?

    Thanks!

    • ambilykk says:

      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

  3. Kristi says:

    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!

  4. KrishnaMG says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s