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.
|Check out our Excel VBA Resources|
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
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
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 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
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