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

jjasmith4

Board Regular
Joined
Aug 22, 2018
Messages
56
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):
VBA Code:
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,818
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Can I ask why you have to do it this way?
 

jjasmith4

Board Regular
Joined
Aug 22, 2018
Messages
56
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,818
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Aug 22, 2018
Messages
56

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2010
Messages
2,447
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Aug 22, 2018
Messages
56
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,447
Office Version
  1. 2013
Platform
  1. Windows
This works for me, no alerts ...

ScreenShot229.jpg


ThisWorkbook module:
VBA Code:
Option Explicit

Private Sub Workbook_Open()
    MsgBox CmdLineToStr
End Sub

Standard module:
VBA Code:
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
 

Forum statistics

Threads
1,148,194
Messages
5,745,276
Members
423,942
Latest member
excelhelp1423

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top