Command Line Arguments

[Guest]

Hi everyone,

I have an Add-In with an Auto_Open procedure that needs to know what (if
any) command line arguments have been supplied when Excel was started.

I have searched Google and found a post that used the GetCommandLineA API
but when I use this in Excel 2003 from VBA I get what appears to be a memory
dump of some kind and sometimes Excel simply crashes. The example can be
found at http://j-walk.com/ss/excel/eee/eee002.txt

Can anyone help me?

My ultimate goal is to allow my users to schedule my Excel Addin to run at
different times with different Command Line parameters.

Many thanks in advance.

Joe

 

[Michel Pierron]

Hi Joseph,

Option Explicit
Private Declare Function GetCommandLine Lib "kernel32" _
Alias "GetCommandLineA" () As Long
Private Declare Function lstrlen Lib "kernel32" Alias _
"lstrlenA" (ByVal lpString As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)

Private Sub Workbook_Open()
Dim CmdLine$, CmdArgs$(), i&, ArgNb&
CmdLine = GetCommLine
If Len(CmdLine) = 0 Then Exit Sub
i = InStr(1, CmdLine, ThisWorkbook.FullName, 1)
If i Then CmdLine = Mid$(CmdLine, 1, i - 1) Else Exit Sub
If Right$(CmdLine, 1) = """" Then i = 2 Else i = 1
CmdLine = Mid$(CmdLine, 1, Len(CmdLine) - i)
CmdLine = Mid$(CmdLine, InStr(1, CmdLine, " /e", 1) + 3, Len(CmdLine)) & "/"
Do Until Len(CmdLine) < 2
i = InStr(CmdLine, "/")
ArgNb = ArgNb + 1
ReDim Preserve CmdArgs(1 To ArgNb)
CmdArgs(ArgNb) = Mid$(CmdLine, 1, i - 1)
CmdLine = Mid$(CmdLine, i + 1, Len(CmdLine))
Loop
For i = 1 To ArgNb
CmdLine = CmdLine & "Parameter " & i & ": " & CmdArgs(i) & vbLf
Next i
MsgBox CmdLine
End Sub

Private Function GetCommLine() As String
Dim Ret&, sLen&, Buffer$
Ret = GetCommandLine
sLen = lstrlen(Ret)
If sLen Then
GetCommLine = Space$(sLen)
CopyMemory ByVal GetCommLine, ByVal Ret, sLen
End If
End Function

Examples of use:
Execute dialog box:
excel /eInfo1/Info2 c:\your file.xls
with short cut:
"C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" /eInfo1/Info2
"c:\your file.xls"
with batch file:
Start "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" /eInfo1/Info2
"c:\your file.xls"

Regards,
MP

 

[Guest]

Thanks Michel. I works perfectly!

Regards,
Joe

Hi Joseph,

Option Explicit
Private Declare Function GetCommandLine Lib "kernel32" _
Alias "GetCommandLineA" () As Long
Private Declare Function lstrlen Lib "kernel32" Alias _
"lstrlenA" (ByVal lpString As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)

Private Sub Workbook_Open()
Dim CmdLine$, CmdArgs$(), i&, ArgNb&
CmdLine = GetCommLine
If Len(CmdLine) = 0 Then Exit Sub
i = InStr(1, CmdLine, ThisWorkbook.FullName, 1)
If i Then CmdLine = Mid$(CmdLine, 1, i - 1) Else Exit Sub
If Right$(CmdLine, 1) = """" Then i = 2 Else i = 1
CmdLine = Mid$(CmdLine, 1, Len(CmdLine) - i)
CmdLine = Mid$(CmdLine, InStr(1, CmdLine, " /e", 1) + 3, Len(CmdLine)) & "/"
Do Until Len(CmdLine) < 2
i = InStr(CmdLine, "/")
ArgNb = ArgNb + 1
ReDim Preserve CmdArgs(1 To ArgNb)
CmdArgs(ArgNb) = Mid$(CmdLine, 1, i - 1)
CmdLine = Mid$(CmdLine, i + 1, Len(CmdLine))
Loop
For i = 1 To ArgNb
CmdLine = CmdLine & "Parameter " & i & ": " & CmdArgs(i) & vbLf
Next i
MsgBox CmdLine
End Sub

Private Function GetCommLine() As String
Dim Ret&, sLen&, Buffer$
Ret = GetCommandLine
sLen = lstrlen(Ret)
If sLen Then
GetCommLine = Space$(sLen)
CopyMemory ByVal GetCommLine, ByVal Ret, sLen
End If
End Function

Examples of use:
Execute dialog box:
excel /eInfo1/Info2 c:\your file.xls
with short cut:
"C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" /eInfo1/Info2
"c:\your file.xls"
with batch file:
Start "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" /eInfo1/Info2
"c:\your file.xls"

Regards,
MP