Command Line Arguments

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

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

"Joseph Hanna" <joe.hanna@no.spam.vantageit.com.au> a écrit dans le message
de news: D625A728-0AD5-4CD3-922B-64A89EB9E936@microsoft.com...
> 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
>
>
>
>
>
>

Thanks Michel. I works perfectly!

Regards,
Joe

"Michel Pierron" wrote:

> 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
>
> "Joseph Hanna" <joe.hanna@no.spam.vantageit.com.au> a écrit dans le message
> de news: D625A728-0AD5-4CD3-922B-64A89EB9E936@microsoft.com...
> > 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
> >
> >
> >
> >
> >
> >
>
>
>