Command line arguments?

Posted by Larry Jones on April 25, 2001 1:02 PM

This is driving me crazy! Surely there is a way to pass an argument to an Excel spreadsheet so the code in the Open event can process the argument!???

I need to open an Excel workbook from an application or from the web interface, passing an value to the workbook that will automatically be found and shown when the workbook opens. I could have the user re-enter the value, but since they will have already done that once, that seems like a waste and I know the user will not like that approach.

The Command() function does not work, and so far I have not had any luck with the GetCommandLine API function either.

I am using NT 4.0 and Excel 97.

Any suggestions?

Posted by Ivan Moala on April 26, 2001 5:52 AM

The following may help you ??

POWER PROGRAMMING TECHNIQUE

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 argumentsfrom an Auto_open
sub in the workbook "c:\temp\test.xls" opened by a batch file (or by a
Win95 shortcut).

1. Your command line should look like this one:

start excel c:\temp\test /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:\temp\file1.dbf",
"all" and "exclusive" to Excel, your command-line should look like:

start excel c:\temp\test /e/c:\temp\file1.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 ArgCount As Integer 'number of parameters
Dim Pos1 As Integer, Pos2 As Integer

CmdLine = GetCommandLineA 'get the cmd-line string
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

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

End Sub

If you use the command-line above, this Auto_open sub will automatically
store the three arguments ("c:\temp\file1.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...).

**********************************

Ivan