Command Line Arguments in VBA

By Laurent Longre

There is a way for an auto-start macro to read the arguments on the command line (with Excel 97).

Assume that you want to read the command line arguments from an Auto_open sub in the workbook “c:\emp\est.xls” opened by a batch file (or by a Win95 shortcut).

1. Your command line should look like this one:

start excel.exe c:\emp\est.xls /e/param1/param2/.../paramN

i.e. : after excel.exe, the name of the workbook containing the Auto_open, then the switch /e **immediately** followed by your own arguments. These arguments should be separated by “/” and form a continuous string without spaces.

For instance, if you want to pass the arguments “c:\empfile1.dbf”,”all” and “exclusive” to Excel, your command-line should look like:

start excel.exe c:\emp\est.xls /e/c:\empfile1.dbf/all/exclusive

2. In Test.xls, use the API function GetCommandLine (alias GetCommandLineA in Win95) to get the contents of this command-line string.

You should then parse the string returned by GetCommandLineA, search for the separators “/” and store each argument in an array. Here is an example of a such Auto_open sub:

If you use the command-line above, this Auto_open sub will automatically store the three arguments (“c:\empfile1.dbf”, “all” and “exclusive”) in the Args() array and display them.

Again, be sure that you don’t insert any space between /e and each argument in the command-line, otherwise it could fail (Excel can believe that these “pseudo-arguments” are the names of workbooks to open at startup…).

Editor’s Note: The command line Laurent gives didn’t work for me, so I changed it to:

excel.exe c:\emp\est.xls /e/c:\empfile1.dbf/all/exclusive

I assume that Windows has changed since this was written. Either that or I’m just missing something. You might notice from the first message box that the CmdLine variable is quite long. I don’t know if that’s a consequence of my change to the command line or if it’s just the way it is in our XP world. It contains a lot of binary data that I can only guess is the Excel file. I shrink it down to 255 characters for ease of use. I expected the last element of Args to be a bunch of crap, but it wasn’t.

I left the original code commented out. I couldn’t get the Search method of the WorksheetFunction class to work without error, so I changed it to Instr. As long as I was changing stuff, I thought I might as well employ the Split function (new in Excel 2000), but there’s certainly nothing wrong with the Laurent’s code as written.

10 thoughts on “Command Line Arguments in VBA

  1. doco: Carry out unattended ‘batch processing’ of XL-based reports.

    Comments on original post: Thanks for that extensive example with the CommandLine API. I briefly explored it before opting for an alternative that works on a machine that has Windows Scripting Host (WSH) installed — nowadays that would be almost all.

    Use a VBScript script (or JScript if you prefer that) that instantiates XL, opens the appropriate workbook, and uses xlApp.Run {subroutine name}, {arguments} to process whatever.

    It was an effective technique at a pharmaceutical company that wanted a variety of reports (including charts) printed in an unattended fashion. Basically, their corporate databases were updated overnight with financial and clinical data. So, at some point each morning this unattended script would kick off and the relevant reports would be waiting for various people when they got to work.

  2. I too came across this article, but it didn’t work out of the box – I had to make some alterations.
    My Auto_Open macro is pretty much identical though.

    I have an external app which has integrated VBA.
    I want to use the Excel spreadsheet like a batch file

    The way you start Excel is important if you want Add-Ins to load. Note that creating an Excel.Application object will not start Add-Ins. Even more frustrating is that my Add-Ins are 2002 Automation Add-Ins. It seems there is no API way of “starting” them if you go the CreateObject method.

    The code as follows (apologies if this code doesnt post right)

    Declare Function ShellExecute Lib “shell32” Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Declare Function GetDesktopWindow Lib “user32” () As Long

    Const SW_HIDE = 0
    Const SW_SHOWNORMAL = 1

    Const cFileName_Interface = “C:MySpreadsheet.xls”

    Sub Main()
        Dim dtm As Date, lng As Long

        dtm = Date – 1

        lng = ShellExecute(GetDesktopWindow, “open”, “Excel”, “””” & cFileName_Interface & “”” /e/BATCH/” & Format(dtm, “yyyymmdd”), vbNullString, SW_HIDE)
    End Sub

  3. Why I get Aplication error command when I run the above file from a batch file?

    The instruction at “0×7C4E7A1F” referenced memory at 0×00184000. Memory cannot be read.

    My Excel version: Microsoft Excel 2000 (9.0.2720).

    Thank you.

  4. Hi:

    When I pasted the above code and tried, the message box does not show any arguments but it only displays the following message like this:
    The arguments are:


    My Excel version is 2000. What am I missing here? Could someone point me out? Thanks for any help.

  5. I tried the command line “start excel c: emp est.xls /e/c: empfile1.dbf/all/exclusive”. Not worked. The parameters after test.xls filename was not received in Auto_run(). After changed the cmd line to “start excel /e/c: empfile1.dbf/all/exclusive c: emp est.xls”, the parameters in the middle was received.

    I am using Excel 2003 on WinXP.

  6. Hi all,

    Although it is specified twice that no space are allowed, I still need to use space. Any ideas of a work around?

    My best alternative is a VBS script that will do the same, but then I ran into the problem of having an excel session impossible to close.

    Thank you for your creative help.

    Note : I just try to pass the parameter of the path of a file that could contain some spaces.


Leave a Reply

Your email address will not be published. Required fields are marked *