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

[DKenny]

Webtest

Add a module and insert the following 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

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