How to pass and get some random string on the command line?

[jjasmith4]

On the Excel command line I want to be able to pass a random string, and I want to be able to read that string in VBA. For example:

"C:\Program Files (x86)\Microsoft Office\root\Office16\excel.exe" CommandLine.xlsm "here's my random string"
Right now in my CommandLine.xlsm I have a simple function which I wrote only to make it visible to the UI -- it's supposed to read the command line using the built-in function Command$ (in VBA.Interaction):

Public Function CommandLine() As String
    CommandLine = Command$()
End Function

I've see all those Windows-API functions that read the command line, but even if I use all those the problem is that 1) Excel thinks "here's my random string" is the name of a file, which it tries to open and obviously can't find, so I get an error, and 2) Command$ always returns a null string even if I pass various known-good switches like /e or /m.

So what I want is a way to get that random string from the Windows command line into a VBA string variable without Excel processing it in any way. In other words, I want to be able to issue the above Excel command line (or some variation) and for Excel to open CommandLine.xlsm with no error messages and for its CommandLine function to return "here's my random string". How can I simply pass any string on the command line that I can read into a string in VBA?

 

[RoryA]

Can I ask why you have to do it this way?

 

[jjasmith4]

Can I ask why you have to do it this way?

OK, I'm writing an XLSM that gathers information about certain financial deals, identified by their deal IDs (unique numbers). It's invoked by a system other people are writing which presents the UI that allows analysts to choose the deals they want to process. The UI gathers deals' IDs, then invokes my XLSM, and I want it to pass me the list of deal IDs on the command line, e.g., "C:\...\Excel.exe MyDealProcessor.xlsm "123,456,789" to process deals 123 and 456 and 789 and then stay open so the UI can read all the results.

I'm also just looking for a general solution like that, the same way you can pass parameters to a Windows batch file (%1, %2, etc.) and you have them in the batch file's code and can do with them what you want.

 

[RoryA]

I don't know of any way to stop Excel from interpreting the string you pass as an additional filename, I'm afraid. I think you'd be better off writing the data to a text file in a specific location, or to the registry.

 

[jjasmith4]

So then what good is Command$, when it always returns a null string? Or, under what conditions does it not return a null string?

 

[GWteB]

So then what good is Command$, when it always returns a null string?

This is a native feature of Visual Basic and VBA stems from it. The Command function returns the command line when VB.exe or a VB compiled executable is launched.
VBA doesn't support this function.

 

[jjasmith4]

This is a native feature of Visual Basic and VBA stems from it. The Command function returns the command line when VB.exe or a VB compiled executable is launched.
VBA doesn't support this function.

OK thanks. But it's pretty strange that since DOS batch in the 80s to now you can get the command line and do what you want batch/script, but MS Office can't do that...

 

[GWteB]

This works for me, no alerts ...

ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    MsgBox CmdLineToStr
End Sub

Standard module:

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
    Declare PtrSafe Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
    Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
#Else
    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)
#End If

Public Function CmdLineToStr() As String
    Dim Buffer() As Byte, StrLen As Long, CmdPtr As Long
    CmdPtr = GetCommandLine()
    If CmdPtr > 0 Then
      StrLen = lstrlenW(CmdPtr) * 2
      If StrLen > 0 Then
        ReDim Buffer(0 To (StrLen - 1)) As Byte
        CopyMemory Buffer(0), ByVal CmdPtr, StrLen
        CmdLineToStr = Buffer
      End If
    End If
End Function