So this is something that I've used in Excel to be able to pass in runtime parameters to a macro enabled workbook, in the example provided I'm passing a path for a csv file for later processing. The way that this has been set up (as you'll see from the code) is to run through the command line arguments presented, strip out the bits that we're interested in and set the values of any desired arguments to variables within the workbook. Note: this code isn't entirely original and the majority of the work was done by the participants on this thread.
The format that this approach expects is a series of key/value pairs separated by a pipe | and serialised by forward slash, after "/e/" to denote that the custom arguments are starting. So something like this:
Depending on how you're actually calling the sheet you'll need to do a few different things. From the command line you could use any of the follwing:
excel.exe "C:\tst.xlsm" /e/csvpath|C:\csPath\thing.csv excel C:\tst.xlsm /e/csvpath|C:\csPath\thing.csv
and from within .Net (C# in this instance), you'd want to put something down that looks like this:
Process.Start(@"excel", @"C:\tst.xlsm /e/csvpath|C:\csPath\thing.csv");
The actual code that you'll want to use is as follows; Paste the following into a module in your workbook so that it can be accessed from anywhere (although that said there's no reason not to put it in the workbook module along with the next chunk):
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
and then within your 'ThisWorkbook' object enter the following. It's pretty well commented so it will take you through what's being done but essentially the code will strip through the user defined arguments as it finds them and then if it finds a key that it's looking for it will store that value into the declared global variable. If you've got a lot of parameters that you're looking to pass then you can add to the if statements at the bottom. I'd also recommend commenting out the MsgBox lines unless you want the functionality to be very noisy!
Public CSVPath As String ' This sub will try and strip out any 'user command line arguments' that are formatted in the following way: ' excel.exe "ThisWSheetPath" /e/key|value [/key|value]... ' The first section dictates which application to use, ' The second (enclosed by double quotes) denotes ' after this use /e/ to begin the user arguments, these should be a key and value separated by a pipe ' for multiple parameters to be passed separate the pairs out with a forward slash: ' /e/csvpath|C:\file.csv/secondparm|secondval ' ' Example: excel.exe "C:\tst.xlsm" /e/csvpath|C:\csPath\thing.csv Private Sub Workbook_Open() Dim CmdRaw As Long Dim CmdLine As String Dim userName As String Dim userPass As String Dim v() As String CmdRaw = GetCommandLine CmdLine = CmdToSTr(CmdRaw) 'MsgBox CmdLine Dim rev As String Dim fn As Integer Dim UsrParms As String Dim SplitParms() As String Dim s As Integer ' Grab only the string that we're interested in after /e/ rev = StrReverse(CmdLine) fn = InStr(1, rev, "/e/", vbTextCompare) - 1 ' did we submit any command line params? If fn > 0 Then UsrParms = StrReverse(Left(rev, fn)) ' split our paramstring out along forward slashes SplitParms = Split(UsrParms, "/") For s = 0 To UBound(SplitParms) Dim kvp() As String kvp = Split(SplitParms(s), "|") ' Let's look at each one 'MsgBox "Key: " & kvp(0) & vbNewLine & " Value: " & kvp(1) If UCase(kvp(0)) = "CSVPATH" Then ' Do something with your found variable! CSVPath = kvp(1) End If Next s End If If SCVPath <> "" Then MsgBox CSVPath End Sub