EXCEL 64 bit command line vba code

Question

I have code to fetch command line arguments when opening up excel book (64 bit; but 32 bit code is also there under #if clause).

So for example, when I run the following line of code at the command prompt, I am expecting to be able to fetch the input string as the command line arguments:

start Excel ".\AwajiPush.xlsm" /p/"kjh%dg.pdf"

(By the way, the reason why "start" is there, is so that it would work in a .bat batch file)

I am expecting to be able to capture ".\AwajiPush.xlsm" and /p/"kjh%dg.pdf" as parameters.

The code doesn't do that.

Why is it not fetching the second argument?

I don't know too much about how pointers work. Is there a piece of code that I can use to capture at least a string that contains both parameters, so that I can parse it. If it contains more, that is fine. I can always interpret it, as long as it is consistent.

I put stubs in the program (MsgBox), and I am not sure why the second stub shows blank.

Here is the code:

'Put this code in a new module called Parameters

Option Explicit

#If Win64 Then
    Private Declare PtrSafe Function GetCommandLineL Lib "kernel32" _
     Alias "GetCommandLineA" () As LongPtr

    Private Declare PtrSafe Function lstrcpyL Lib "kernel32" _
     Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As LongPtr) As Long

    Private Declare PtrSafe Function lstrlenL Lib "kernel32" _
     Alias "lstrlenA" (ByVal lpString As LongPtr) As Long


 #Else
    Private Declare Function GetCommandLineL Lib "kernel32" _
     Alias "GetCommandLineA" () As Long

    Private Declare Function lstrcpyL Lib "kernel32" _
     Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As Long) As Long

    Private Declare Function lstrlenL Lib "kernel32" _
     Alias "lstrlenA" (ByVal lpString As Long) As Long

 #End If

 Function GetCommandLine() As String
   Dim strReturn As String
   #If Win64 Then
   Dim lngPtr As LongPtr
   #Else
   Dim lngPtr As Long
   #End If

   Dim StringLength As Long
   'Get the pointer to the commandline string
   lngPtr = GetCommandLineL

   'get the length of the string (not including the terminating null character):
   StringLength = lstrlenL(lngPtr)
   MsgBox StringLength


   'initialize our string so it has enough characters including the null character:
   strReturn = String$(StringLength + 1, 0)
   'copy the string we have a pointer to into our new string:
   MsgBox strReturn


   lstrcpyL strReturn, lngPtr
   'now strip off the null character at the end:
   MsgBox strReturn


   GetCommandLine = Left$(strReturn, StringLength)

 End Function

And

'Put this code in "This Workbook"

Sub workBook_open()
    MsgBox Parameters.GetCommandLine
End Sub

Arnold

Answer 1

Here's a function to get the command line from the current process:

Private Declare PtrSafe Function w_commandline Lib "kernel32.dll" Alias "GetCommandLineW" () As LongPtr
Private Declare PtrSafe Function w_strlen Lib "kernel32.dll" Alias "lstrlenW" (ByVal lpString As LongPtr) As Long
Private Declare PtrSafe Sub w_memcpy Lib "kernel32.dll" Alias "RtlMoveMemory" (dst As Any, src As Any, ByVal size As LongPtr)

Public Function GetCommandLine() As String
  GetCommandLine = String$(w_strlen(w_commandline()), 0)
  w_memcpy ByVal StrPtr(GetCommandLine), ByVal w_commandline(), LenB(GetCommandLine)
End Function

Sub Test()
  Debug.Print GetCommandLine()
End Sub

Note that you'll have to use the /e switch to avoid a redirection to an already launched instance of Excel and thus to keep the provided parameters. For example:

excel.exe /e "C:\temp\myfile.xlsm" /p "myparam"

Or with start:

start "xl" excel.exe /e "C:\temp\myfile.xlsm" /p "myparam"

But if your goal is to provide some arguments to VBA from a batch, then use an environment variable:

Set MyArguments=abcde
start "xl" excel.exe /e "C:\temp\myfile.xlsm"

, then to get the argument from Excel:

Debug.Print Environ("MyArguments") ' >> "abcde" '

Heaves

On Windows 11, if i use set at command-line (not in a batch) to create an environment variable, then VBA Environ fails to return that variable. It returns an empty string. Eg do at command prompt: set test=abc – Ml

It seems your solution returns the ENTIRE command line, not just the arguments. In your example, i think we'd have to write some string manipulation to pull everything after /p. But do we have to use /p. Can we use a character other than / or a letter other than p? – Ml

i understand now why env vars assigned with set aren't available to VBA outside of a batch. set env vars are only avail in process. With a batch, Excel VBA is running in same batch process as set, so VBA can see the env vars. – Ml

Answer 2

According to the Microsoft documentation as of 2016, none of the Office products treat the /P switch as a "command line parameter" switch.

Baccy