Command Line Arguments

  • Thread starter Thread starter Michael
  • Start date Start date
M

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
 
Michael said:
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.
Click to expand...

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
 
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.
 
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.
Click to expand...

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"
 
Back
Top