Command Line Arguments

[Michael]

I have an Excel Workbook that, when opened, runs a
particular macro. The macro requires a few arguments from
the user, in order to run. Rather than have the user
specify them as the macro is running, I'd like to have the
user specify them when they open the Workbook (from the
command line). Is there a way to specify command line
arguments to Excel, and if so, how can I access them from
a macro? Thanks.

Michael

 

[Keith Willshaw]

I have an Excel Workbook that, when opened, runs a
particular macro. The macro requires a few arguments from
the user, in order to run. Rather than have the user
specify them as the macro is running, I'd like to have the
user specify them when they open the Workbook (from the
command line). Is there a way to specify command line
arguments to Excel, and if so, how can I access them from
a macro? Thanks.

You cant use the command line arguments since Excel treats all command line
parameters (other than option switches) as names of files it should open.

You could use VB6 or VBscript to automate Excel and then run the
macros with the required data using the Application.Run syntax

Sub Main
Dim objExcel As Object
Set objExcel = CreateObject( "Excel.Application")
objExcel.application.workbooks.open "c:\YourDirectory\YourWorkBook.XLS"

' run macro from workbook
objExcel.application.Run ("YourWorkbook.XLS!.Yourmodule.YourMacro"
,YourArg1, YourArg2)

Set objExcel = Nothing 'clear object from memory

End Sub

 

[pxd74]

You can use API function GetCommandLine. Here is an example form archive:
http://www.google.pl/groups?hl=pl&lr=&ie=UTF-8&oe=UTF-8&[email protected]&rnum=6

 

[Tom Ogilvy]

Don't know who your target audience is, but I believe Laurent later reported
that this method does not work in NT and its offspring.

 

[pxd74]

Don't know who your target audience is, but I believe Laurent later

reported that this method does not work in NT and its offspring.

Yes. It's true, but example below works perfect in NT system.

Option Explicit
Private Declare Function GetCommandLine Lib "kernel32" Alias _
"GetCommandLineA" () As Long
Private Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" _
(ByVal lpString1 As String, ByVal lpString2 As Long) As Long

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
Dim PosSpace As Integer, PosSlash As Integer
Dim bEnd As Boolean
Dim i As Long

CmdLine = CommandLine
Pos1 = InStr(1, CmdLine, "/e") + 2 'search "/e"
If Pos1 = 2 Then Exit Sub
Do While bEnd = False
PosSlash = InStr(Pos1, CmdLine, "/") + 1
Pos1 = PosSlash
PosSpace = InStr(Pos1, CmdLine, " ")
PosSlash = InStr(Pos1, CmdLine, "/")
If PosSlash = 0 Then
Pos2 = PosSpace
Else
Pos2 = WorksheetFunction.Min(PosSpace, PosSlash)
End If
ArgCount = ArgCount + 1
ReDim Preserve Args(ArgCount)
Args(ArgCount) = Mid(CmdLine, Pos1, Pos2 - Pos1)
If PosSlash > PosSpace Or PosSlash = 0 Then
bEnd = True
End If
Loop
For i = 1 To ArgCount
MsgBox "Argument " & ArgCount & " : " & Args(i)
Next
End Sub

Private Function CommandLine() As String
Dim lpStr As Long, i As Long
Dim buffer As String

lpStr = GetCommandLine()
buffer = Space$(512)
lstrcpy buffer, lpStr
buffer = Left$(buffer, InStr(buffer & vbNullChar, vbNullChar) - 1)
CommandLine = buffer
End Function


You should pass arguments in this way:
- path to excel.exe with quotation mark
- space
- /e
- other arguments followed by slash (can't contain space)
- space
- file to open with quotation marks

For example:
"C:\Program Files\Microsoft Office\Office10\EXCEL.EXE"
/e/print/something/argument3 "C:\Zeszyt1.xls"