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
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
Args = Split(Mid(CmdLine, Pos1, 255), "/")
For ArgCount = LBound(Args) To UBound(Args)
sMsg = sMsg & Args(ArgCount) & vbNewLine
MsgBox "The arguments are:" & vbNewLine & vbNewLine & sMsg
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.