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:

Option Base 1
Declare Function GetCommandLineA Lib "Kernel32" () As String
 
Sub Auto_open()
 
    Dim CmdLine As String  'command-line string
    'Dim Args() As String  'array for storing the parameters
    Dim Args As Variant
    Dim ArgCount As Integer  'number of parameters
    Dim Pos1 As Integer, Pos2 As Integer
    Dim sMsg As String  'added by dk
   
    CmdLine = GetCommandLineA  'get the cmd-line string
   
    '**Added by DK - check this out
    MsgBox "Then length of the command line is " & Len(CmdLine)
    CmdLine = Mid(CmdLine, 1, 255)
    '**End added by DK
   
    On Error Resume Next  'for the wksht-function "Search"
   'Pos1 = WorksheetFunction.Search("/", CmdLine, 1) + 1  'search "/e"
   'Pos1 = WorksheetFunction.Search("/", CmdLine, Pos1) + 1  '1st param
    Pos1 = InStr(1, CmdLine, "/") + 1
    Pos1 = InStr(Pos1, CmdLine, "/") + 1
    
'    Do While Err = 0
'      Pos2 = WorksheetFunction.Search("/", CmdLine, Pos1)
'      ArgCount = ArgCount + 1
'      ReDim Preserve Args(ArgCount)
'      Args(ArgCount) = Mid(CmdLine, Pos1, _
'        IIf(Err, Len(CmdLine), Pos2) - Pos1)
'      MsgBox "Argument " & ArgCount & " : " & Args(ArgCount)
'      Pos1 = Pos2 + 1
'    Loop
'
    Args = Split(Mid(CmdLine, Pos1, 255), "/")
    
    For ArgCount = LBound(Args) To UBound(Args)
        sMsg = sMsg & Args(ArgCount) & vbNewLine
    Next ArgCount
    
    MsgBox "The arguments are:" & vbNewLine & vbNewLine & sMsg
    
End Sub

Option Base 1

Declare Function GetCommandLineA Lib "Kernel32" () As String

Sub Auto_open()

Dim CmdLine As String 'command-line string

'Dim Args() As String 'array for storing the parameters

Dim Args As Variant

Dim ArgCount As Integer 'number of parameters

Dim Pos1 As Integer, Pos2 As Integer

Dim sMsg As String 'added by dk

CmdLine = GetCommandLineA 'get the cmd-line string

'**Added by DK - check this out

MsgBox "Then length of the command line is " & Len(CmdLine)

CmdLine = Mid(CmdLine, 1, 255)

'**End added by DK

On Error Resume Next 'for the wksht-function "Search"

'Pos1 = WorksheetFunction.Search("/", CmdLine, 1) + 1 'search "/e"

'Pos1 = WorksheetFunction.Search("/", CmdLine, Pos1) + 1 '1st param

Pos1 = InStr(1, CmdLine, "/") + 1

Pos1 = InStr(Pos1, CmdLine, "/") + 1

' Do While Err = 0

' Pos2 = WorksheetFunction.Search("/", CmdLine, Pos1)

' ArgCount = ArgCount + 1

' ReDim Preserve Args(ArgCount)

' Args(ArgCount) = Mid(CmdLine, Pos1, _

' IIf(Err, Len(CmdLine), Pos2) - Pos1)

' MsgBox "Argument " & ArgCount & " : " & Args(ArgCount)

' Pos1 = Pos2 + 1

' Loop

Args = Split(Mid(CmdLine, Pos1, 255), "/")

For ArgCount = LBound(Args) To UBound(Args)

sMsg = sMsg & Args(ArgCount) & vbNewLine

Next ArgCount

MsgBox "The arguments are:" & vbNewLine & vbNewLine & sMsg

End 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”

Just curious: Is there an example in the post 1992 world of why a command line would be used like this?

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.

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

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

Details:
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.

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:

133612
————

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

I got an Application error command when I ran the above command line, but I removed the “Option base 1? line, and it worked pretty nice then (Excel 2002)

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.