Excel: How to: Pass Command Line Parameter [DKenny is KING!]

Esteemed Forum Participants and Lurkers:
===============================
Excel 2003 VBA

I vaguely recall having done this somewhere in the way distant past ... can I pass a general purpose variable (department name string in this case) as a Command Line Parameter to Excel? Right now I have 6 different workbooks for 6 departments with macros, and I want to consolidate them all into one workbook with 6 shortcuts, where the shortcut provides the department via a Command Line Parameter. Except for the department name and folder path, the Macro is identical for each department. I know I can specify a startup file in the Command Line, but can I use a general purpose parameter? Isn't there some way to fetch the parameter in the Workbook Open event?

Is there perhaps an API call that can fetch the command line so that I can extract my parameter?

This is still gnawing at my mind ... I tried messing with the "Start In" parameter in a Shortcut, but I can't figure out how that has any effect at all on Excel, or how to read the value from a VBA function.

I've played with "Application.DefaultFilePath" and "Application.AltStartupPath", but the "Start In" in the shortcut doesn't seem to affect either of them. Yuk.

Nobody has any ideas whatsoever on passing parameters to Excel ???

Webtest

Add a module and insert the following code

VB Code:

Option Base 0
Option Explicit
 
Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
 
Function CmdToSTr(Cmd As Long) As String
Dim Buffer() As Byte
Dim StrLen As Long
   
   If Cmd Then
      StrLen = lstrlenW(Cmd) * 2
      If StrLen Then
         ReDim Buffer(0 To (StrLen - 1)) As Byte
         CopyMemory Buffer(0), ByVal Cmd, StrLen
         CmdToSTr = Buffer
      End If
   End If
End Function

Then in the Open event Proc for the workbook add the following

VB Code:

Private Sub Workbook_Open()
Dim CmdRaw As Long
Dim CmdLine As String
    
    CmdRaw = GetCommandLine
    CmdLine = CmdToSTr(CmdRaw)
    MsgBox CmdLine
End Sub

Now, try passing an Excel command line with the /e switch followed by your list of variables, all seperated by forward slashes.
Here's the command line I was using.

"excel \\AUSCSRR206\DECLAN_X_KENNY$\DK_Test.xls /e/dk/var2"

The e/ switch is vital here as you don't want Excel to open with a blank workbook.


the Open code snippet above just shows you the string value. I haven't written the code to parse out the values at the end of the string, but that's the easy piece ;)

Hot Damn! A Rating? Hey, where do I send the check!!! Thank you, thank you, thank you!

It took me about 7 minutes of munging around to finally get it to work. I tried a Batch file first but couldn't get it to work. Then I drug a copy of my dearly beloved "Excel" shortcut onto my desktop and here is the "Command Line" in my new Shortcut:

Code:

---

"C:\Program Files\Microsoft Office XP\OFFICE11\EXCEL.EXE" "S:\Intranet\Spreadsheets\Experiments\AutoOpenParam.xls" /e/MyParam

---

... and it WORKS!!! I could kiss you, the beautiful message box that pops up, and the horse! *

Glad I could help, but lets hold off on the kissing - focus more on the beer.... :bigyello:

How do I parse the value from the string?

Wow, this is a blast from the past!

I would suggest reversing the string, using the StrReverse function.
Then use the Find Fuction to locate the 'e' switch.
Take a sub-string of the string to the location of the 'e' swith
Then re-reverse using StrReverse
Then use the Split function with the / as a delimiter and you have an array of your Parameter values.

Quote:

---

Originally Posted by DKenny

Wow, this is a blast from the past!

I would suggest reversing the string, using the StrReverse function.
Then use the Find Fuction to locate the 'e' switch.
Take a sub-string of the string to the location of the 'e' swith
Then re-reverse using StrReverse
Then use the Split function with the / as a delimiter and you have an array of your Parameter values.

---

Yeah sorry man I came across this after a google search and neglected to check the date.
I managed to get it working with this
v = Split(CmdLine, "/")
Param = v(UBound(v))

Could that cause any issues?

This is an old post, but just picking it up...

Specifically, how do I pass an Excel Command Line I want it to pass - by default, the command line that is passed through is "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE" /e. I cannot see how to pass through command lines like the examples in this thread.

Cheers


Russ

what are you trying to do? this example is for adding custom parameters to command lines, for normal excel parameters, it is not required to do any of this

Hi,

Even i need to pass two values to excel file from command line. Like first name and last name to already created excel file which has workbook name as folders. Passed value should go to A2 and B2 and should then excute the marco named get_ACLs and delete the value from A2 and B2 after 5 mins.

Thanks,
Vipin

sorry, worksheet named folders.

I've used this for a number of years. Here's some help. Please note a few things about my post:

1. I'm not an Excel MVP
2. I don't have the time necessary to be an Excel MVP (these folks earn this title!)
3. Given the above, my code is based from MVP input but there are multiple ways to skin a cat so please feel free to provide input/corrections as necessary.
4. This process is best left to intermediate and advanced Excel VB programmers -- beginners beware!
5. Lastly, I'm not in any one forum for long so though I've used this resource multiple times consider me more of a lurker than a participant i.e., asking me a direct question may not get an answer from me :blush:

Ok, here's what I use:
Create a module named "CommandLineArgs" and paste the below into it:

Code:

---

Option Explicit
Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
'

Public Function CmdToStr(Cmd As Long) As String
'Gets command line arguments/parameters and returns them as a string.
'From:  http://www.vbforums.com/showthread.php?t=366559&highlight=CmdToStr%28Cmd+Long%29
'To use, " /e" MUST be the first parameter.  How multiple parameters are separated is up to the programmer.
'Basic use (MUST be done this way!!):
'  "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" "c:\Reporting\ubase\Flash Report - v6.xlsb" /e /firstkey:value [/secondkey:value...]
'

Dim Buffer() As Byte, StrLen As Long

If Cmd Then
  StrLen = lstrlenW(Cmd) * 2
  If StrLen Then
      ReDim Buffer(0 To (StrLen - 1)) As Byte
      CopyMemory Buffer(0), ByVal Cmd, StrLen
      CmdToStr = Buffer
  End If
End If
End Function

Function ParseArgs(sCmdLine As String) As Variant
'Pulls the command line arguments/parameters and returns them as an array.
'My method for command line arguments.  Toby Erkson.

Dim iStart As Integer, sArgs As String, vArgs As Variant, vTemp As Variant, vReturn() As Variant
Dim x As Integer, lUpperLimit As Long, vDummy(0, 0) As Variant

'Set up error return value
vDummy(0, 0) = False
ParseArgs = vDummy

iStart = InStr(1, sCmdLine, " /e")
If iStart = 0 Then Exit Function  'Couldn't find ' /e' so assume no parameters were given
sArgs = Mid$(sCmdLine, iStart + 4)
If Len(sArgs) = 0 Then Exit Function  'No command line parameters were given

'Loop thru the arguments and fill array.
'index(n, 0) is the key or defined parameter
'index(n, 1) is the user supplied value
vArgs = Split(sArgs, "/")
lUpperLimit = UBound(vArgs)
ReDim vReturn(lUpperLimit - 1, 1)  '"Adjusts" the final array "down" one index
For x = 1 To lUpperLimit  'Skip the first index since it's always empty (thus the reason for the above line)
    vTemp = Split(vArgs(x), ":")  'Break up the arguements (key:value)
    vReturn(x - 1, 0) = vTemp(0)  'Key
    If UBound(vTemp) = 1 Then
        vReturn(x - 1, 1) = Trim(vTemp(1))  'Value.  Remove leading/trailing spaces...especially trailing!
    Else
        vReturn(x - 1, 1) = ""
    End If
Next x

ParseArgs = vReturn
End Function

---

I would recommend exporting this module so it can be reused ;) Don't worry about the code in this, it's just for getting the command line arguments. The code below is where you process those arguments.

Next, in the "ThisWorkbook" module copy the below into it:

Code:

---

Option Explicit
Private Declare Sub ExitProcess Lib "kernel32" (ByVal exitCode As Long)
'ExitProcess (ByVal exitCode as Long)
'

Private Sub Workbook_Open()
'Allows scheduler to automatically run the report if /auto is present in the command line args list.
Dim CmdLine As String, Args As Variant, x As Variant, sUserID As String, sPassword As String
Dim strProcess As String, lExitCode As Long, bProcess As Boolean

'Application.EnableCancelKey = xlDisabled  'Turn off the Esc key
bProcess = False  'Set default
CmdLine = CmdToStr(GetCommandLine)  'Gets the command line string
Args = ParseArgs(CmdLine)  'Returns just the command line arguments

If Args(0, 0) = False Then
    'No command line arguments/parameters were passed in so don't perform automation!
    Exit Sub  'We want the ability to open the workbook w/o it executing code automatically
'    lExitCode = 5  'Alternately, we can remove the above line, uncomment this section, and throw an error if no command line parameters we issued
'    GoTo Exit_Process
End If

For x = 0 To UBound(Args)  'Pull necessary data from the arguments
    If LCase(Args(x, 0)) = "auto" Then bProcess = True
Next x

If bProcess Then
    lExitCode = 0
    GenerateReport  'Perform the magic!  Here's where you put the name of the procedure you want to execute.
Else
    'Oops, not all arguments were found so throw error!
    lExitCode = 2
End If

Exit_Process:
ExitProcess (lExitCode)  'This acts as a Quit command.  Must be the VERY last command otherwise processes could hang!

End Sub

---

For this EXAMPLE, the procedure "GenerateReport" is called if the command "/auto" is found. Naturally, you can use a differently named procedure. The loop in the above code

Code:

---

For x = 0 To UBound(Args)  'Pull necessary data from the arguments
    If LCase(Args(x, 0)) = "auto" Then bProcess = True
Next x

---

is where you'd check for command line arguments passed in. An example:

Code:

---

For x = 0 To UBound(Args)  'Pull necessary data from the arguments
    If LCase(Args(x, 0)) = "auto" AND LCase(Args(x, 1)) = "true" Then bProcess = True
    If LCase(Args(x, 0)) = "auto" AND LCase(Args(x, 1)) = "false" Then bProcess = False
Next x

---

Parameters are separated by the forward slash "/". This is how the code breaks them up in the procedure "ParseArgs". The arguments are separated by the colon ":". Arguments are made up of a key and a value. For example, the key above is "auto" and the possible values are "true" and "false". Thus, the array of arguments will be two dimensional with the first element containing the key and the second element containing the value. For those that prefer a different way to identifying command line parameters this code is easily changed to accommodate...just remain consistent!

Hope this helps someone in the future :D

Note that your code doesn't seem to retrieve the command line (i.e. you declare but never call GetCommandLine).

Yes I do, second code block, about mid-way:

Code:

---

CmdLine = CmdToStr(GetCommandLine)  'Gets the command line string

---

Curious if there is a 64-bit solution to the issue of processing command line parameters.

i have never tried, but getcommandlineex API may work
i seem to remember getcommandine did not work in W7 32 even

i may still have the code i used, (which was posted in this forum), will look later

Fantastic post!

Got command line parameters recognized in VBA using Excel 2010 and Vista 64 bit using Air_Cooled_Nut's code.

One thing to add - don't use a parameter (after the /e) starting with /s . It will open Excel in Safe mode. e.g. Excel "Workbookname" /e /Shift:Dayshf
This had me stumped for quite a while.

The same might also apply to some of the other predefined Excel command line switches, especially /m as it requires no other arguments. I have not tested against these though.

'/r "workbookpath\file name" Opens a specific workbook as read-only.
'/t "workbookpath\file name" Starts Excel and opens the specified file as a template.
'/n "workbookpath\file name" Like /t, starts Excel and opens the specified file as a template.
'/p "workbookpath" Specifies a folder as the active working folder (for example, the folder that is pointed to in the Save As dialog box).
'/s or /safemode Forces Excel to bypass all files that are stored in startup directories, such as the default XLStart folder
'/m Creates a new workbook that contains a single XLM macro sheet.
'/a progID Starts Excel and loads the Automation Addin that is specified by the progID of the add-in.


Also in reply to Mutantsubhumans question ....there is excellent information on the topic of 64 bit Office and API declarations at
http://www.jkp-ads.com/articles/apideclarations.asp

Peter

Here is a potentially simpler idea to solve this problem - call Excel from a batch procedure and set the arguments as an environment variable in the batch procedure prior to the call, e.g. suppose you want to call the batch file "SpecialExcelApp" (and one nice thing about this is you can give the batch procedure a name that is descriptive of what it does):

In SpecialExcelApp.bat:
set ExcelArgs=%1 %2 %3 %4 %5 %6 %7 %8 %9
c:\Path\To\Excel.exe

Then in Excel VBA, you can access the ExcelArgs environment variable using the Environ() function:
Dim ExcelArgs as String
ExcelArgs = Environ("ExcelArgs")

A couple of other nice things about this approach - the ExcelArgs variable will disappear once the batch procedure exits, so it is sort of hidden and leaves no side effects; and this will work with any version of the OS whether 32- or 64-bit.

Does anyone know why you can't just use the Command function in the VBA Standard Library? I tried it, and it returns an empty string. If it worked, it would be a more elegant solution.

from VBA help

Quote:

---

Command Function


Returns the argument portion of the command line used to launch Microsoft Visual Basic or an executable program developed with Visual Basic. The Visual Basic Command function is not available in Microsoft Office applications.

---

Interesting, but the VBA Language Specification says:
6 VBA Standard Library
6.1 VBA Project
"VBA" is the project name (section 4.1) of a host project (section 4.1) that is present in every VBA Environment. The VBA project consists of a set of classes, functions, Enums and constants that form VBA’s standard library.
6.1.2.8.1.3 Command
Function Declaration
Function Command() As Variant
Function Command$() As String
Runtime Semantics.
Returns the argument portion of the implementation dependent command used to initiate execution of the currently executing VBA program.
The runtime semantics of Command$ are identical to those of Command with the exception that the declared type of the return value is String rather than Variant.

How do we reconcile those two?

Quote:

---

initiate execution of the currently executing VBA program.

---

vba program
not office (or other) application

Quote:

---

How do we reconcile those two?

---

it does not return the command line aarguments