Results 1 to 23 of 23

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

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Resolved 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?
    Last edited by Webtest; Nov 8th, 2005 at 04:17 PM.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  2. #2

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Excel: How to: Pass Command Line 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 ???
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  3. #3
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Excel: How to: Pass Command Line Parameter ???

    Webtest

    Add a module and insert the following code
    VB Code:
    1. Option Base 0
    2. Option Explicit
    3.  
    4. Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
    5. Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
    6. Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
    7.  
    8. Function CmdToSTr(Cmd As Long) As String
    9. Dim Buffer() As Byte
    10. Dim StrLen As Long
    11.    
    12.    If Cmd Then
    13.       StrLen = lstrlenW(Cmd) * 2
    14.       If StrLen Then
    15.          ReDim Buffer(0 To (StrLen - 1)) As Byte
    16.          CopyMemory Buffer(0), ByVal Cmd, StrLen
    17.          CmdToSTr = Buffer
    18.       End If
    19.    End If
    20. End Function

    Then in the Open event Proc for the workbook add the following
    VB Code:
    1. Private Sub Workbook_Open()
    2. Dim CmdRaw As Long
    3. Dim CmdLine As String
    4.    
    5.     CmdRaw = GetCommandLine
    6.     CmdLine = CmdToSTr(CmdRaw)
    7.     MsgBox CmdLine
    8. 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
    Last edited by DKenny; Nov 8th, 2005 at 04:46 PM.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Excel: How to: Pass Command Line Parameter ???

    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! *
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  5. #5
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

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

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

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  6. #6
    New Member
    Join Date
    Feb 2007
    Posts
    2

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

    How do I parse the value from the string?

  7. #7
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

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

    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.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  8. #8
    New Member
    Join Date
    Feb 2007
    Posts
    2

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

    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?

  9. #9
    New Member
    Join Date
    Feb 2010
    Posts
    1

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

    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

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

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

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11
    New Member
    Join Date
    Feb 2010
    Posts
    2

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

    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

  12. #12
    New Member
    Join Date
    Feb 2010
    Posts
    2

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

    sorry, worksheet named folders.

  13. #13
    New Member
    Join Date
    Jun 2011
    Location
    Portland, Oregon
    Posts
    4

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

    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


    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
    Last edited by Air_Cooled_Nut; Feb 23rd, 2012 at 04:46 PM.

  14. #14
    New Member
    Join Date
    May 2011
    Posts
    2

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

    Note that your code doesn't seem to retrieve the command line (i.e. you declare but never call GetCommandLine).
    Last edited by StevenHB; Oct 13th, 2011 at 04:57 PM.

  15. #15
    New Member
    Join Date
    Jun 2011
    Location
    Portland, Oregon
    Posts
    4

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

    Yes I do, second code block, about mid-way:
    Code:
    CmdLine = CmdToStr(GetCommandLine)  'Gets the command line string

  16. #16
    New Member
    Join Date
    Mar 2013
    Posts
    1

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

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

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

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

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18
    New Member
    Join Date
    Mar 2013
    Posts
    1

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

    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 /Shiftayshf
    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

  19. #19
    New Member
    Join Date
    Aug 2014
    Posts
    1

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

    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.

  20. #20
    New Member
    Join Date
    Apr 2015
    Posts
    2

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

    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.

  21. #21
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

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

    from VBA help
    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.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  22. #22
    New Member
    Join Date
    Apr 2015
    Posts
    2

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

    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?

  23. #23
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

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

    initiate execution of the currently executing VBA program.
    vba program
    not office (or other) application

    How do we reconcile those two?
    it does not return the command line aarguments
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width