Passing a parameter to an Excel file when opening it

I would like to know if it is possible to pass a parameter to an Excel file when calling that file from within another Excel file using VBA. I am using the 2007 version. An example of what I would like to do is what we use to do with DOS programs and command line parameters. I would like to have the Excel file I just opened do something depending on the parameter passed to it and then close.

Might be an easy question but I'm stumped. Any ideas?

November 7th, 2012 5:45am

Step.1 Create a macro-enabled workbook

Step.2 Open the workbook, and open the VBA, insert a moudle, copy and paste the following code sample in the module

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

Step.4 Copy and paste the following code sample into ThisWorkbook

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

Step.4 Sign your code or modify the macro security settings to allow the macro to run automatically

Step.5 Save and close the workbook

Step.6 Run command line with the parameter, for example:

Excel "Server\ShareFolder\TheWorkbook.xlsm" /e/myParam

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  • The code sample above will pass the last 6 characters (modify the code if you need more or less) to the workbook
  • The workbook will display a message box to display the parameter we passed from the command line, add the other function as you need

The code sample refers to the reply from Air_Cooled_Nut in

http://www.vbforums.com/showthread.php?366559-Excel-How-to-Pass-Command-Line-Parameter-DKenny-is-KING!

Free Windows Admin Tool Kit Click here and download it now
November 8th, 2012 11:45am

Thanks for the help Max. I'll give it a try.
November 9th, 2012 4:48am

This does not appear to work on my Windows 8.1 machines. Any ideas?
Free Windows Admin Tool Kit Click here and download it now
February 13th, 2015 7:17am

This does not appear to work on my Windows 8.1 machines. Any ideas?

I just verified it still work on Windows 8.1, please make sure you follow those steps correctly.
February 14th, 2015 8:43pm

This works fine when the first excel file is opened from the command line. With this I mean, that excel was not running.

However when just the xlsx file is closed, meaning that the excel application remains open and I open the second xls file, the above function will still display the parameters from the first call, which opened the excel application.

Any ideas on how to fix this. 

Free Windows Admin Tool Kit Click here and download it now
February 20th, 2015 10:28am

This works fine when the first excel file is opened from the command line. With this I mean, that excel was not running.

However when just the xlsx file is closed, meaning that the excel application remains open and I open the second xls file, the above function will still display the parameters from the first call, which opened the excel application.

Any ideas on how to fix this.

February 20th, 2015 10:58pm

I have two machines. One with Windows 7 and the other Windows 8.1. A spreadsheet with the above code works fine on the Windows 7 machine. When I copy the shortcut with the command line  and that same file to the Windows 8.1 machine no parameter is passed. Have you had a similar experience where the 8.1 situation was successful? Please tell me if you had different specifications for the Windows 8.1 machine.
Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2015 9:05am

Perhaps it's important to note that both of my machines are 64 bit.So the code in the above "moudle" requires "PtrSafe" to be added. Perhaps your successful experience was on a 32 bit machine.
February 22nd, 2015 9:12am

Another difference is my use of a shortcut to execute the code. Did you try that in your successful execution under Windows 8.1?
Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2015 9:19am

I found the difference. For Windows 7 having the "/e" wasn't critical. For Windows 8.1 it is.

Thank you.

February 22nd, 2015 9:28am

I found the difference. For Windows 7 having the "/e" wasn't critical. For Windows 8.1 it is.

Thank you.


So, problem solved?
Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2015 2:25am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics