Persisting Command Line Arguments

[Foo_Man_Chu]

I'm opening a spreadsheet two different ways: first by clicking on the icon for the spreadsheet and second via the command line so I can use command line arguments. My problem is this: when i open it via the command line it works as expected. When I open it by clicking on the icon it opens as if it has the command line passed in to it. How can i "flush" the command line parameter after it opens so that if I want to open it via clicking the icon and thus not wanting to have it run as if the command line argument was passed to it?

Here is my code:

Option Base 0
Option Explicit
 'api call for obtaining the username
Private Declare Function GetUserName& Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long)
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


Public Sub Auto_Open()
    
    Dim CmdRaw As Long
    Dim CmdLine As String
    Dim myParam As String
    
    CmdRaw = GetCommandLine
    CmdLine = CmdToSTr(CmdRaw)
    myParam = Right(CmdLine, 4)
    If myParam = "AUTO" Then
     ...........

 

[Leith Ross]

Hello Fu_Man_Chu,

Have you tried writing a buffer of zeroes to the the command line? The maximum length in Windows is 8192 characters.

 

[Foo_Man_Chu]

I did think of that but I couldn't come up with where to do it at in the code. Any suggestions?

 

[Jerry Sullivan]

The command line being read is the command that was used to open the Application (the current Excel session).

That won't be useful if the ActiveWorkbook with this Autpen code is opened (be either shortcut) when another workbook was already opened. That's probably the scenario you are seeing as not working, since the code you are using should work if Excel was closed before opening this workbook with either method.

An alternative would be to use a .bat file that would first store a temporary variable, then run your command to open the workbook. The variable would store a current date-timestamp when the bat file is run. Use the Workbook_Open event in your workbook to compare that stored date-timestamp with the current date-time. If the stamp was made within the previous X seconds, then you could assume that the .bat file was used to open the workbook.

You noted that the switch was passing a parameter (like "AUTO") to be read by VBA. If this parameter varies and you were using it for more than to identify how the workbook was opened, you could use the .bat file to store an additional variable to hold that parameter.

I'd suggest storing the variable in the environment variables, but you could store them to a temporary .txt file if you prefer.

 

[Jaafar Tribak]

As Jerry kindly pointed out, this won't work if the excel application session (ie:- with the switch param) wasn't closed before the workbook is opened.
However, instead of using a bat file as suggested, you could simply edit the commandline string to remove the 'AUTO' param upon closing the workbook in the Workbook_BeforeClose event.

Here is an example : (code goes in the Thisworkbook module)

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetCommandLine Lib "kernel32" Alias "GetCommandLineA" () As LongPtr
    Private Declare PtrSafe Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As LongPtr) As Long
    Private Declare PtrSafe Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As LongPtr, ByVal lpString2 As String) As LongPtr
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal length As LongPtr)
#Else
    Private Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineA" () As Long
    Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Long) As Long
    Private Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As Long, ByVal lpString2 As String) As Long
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal length As Long)
#End If

Private Sub Workbook_Open()

    Const myParam = "AUTO"

    If InStr(1, GetCommLine, myParam) Then
        MsgBox "the command-line string for the current excel session contains the parameter : 'AUTO' "
    Else
         MsgBox "the command-line string for the current excel session DOESN'T contain the parameter : 'AUTO' "
    End If
    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Const myParam = "AUTO"
    Dim sCommadLine As String
    
    Debug.Print GetCommLine [COLOR=#008000]'returns the command-line with the "AUTO" parameter if it exists[/COLOR]
    
    sCommadLine = Replace(GetCommLine, myParam, "")
    lstrcpy GetCommandLine, sCommadLine
    
    Debug.Print GetCommLine [COLOR=#008000]'the command-line parameter is now removed[/COLOR]
    
End Sub

Private Function GetCommLine() As String

    Dim lpCommandLinePtr As LongPtr, lStringLen As LongPtr
    
    lpCommandLinePtr = GetCommandLine
    lStringLen = lstrlen(lpCommandLinePtr)
    If lStringLen > 0 Then
        GetCommLine = Space$(CLng(lStringLen))
        CopyMemory ByVal GetCommLine, ByVal lpCommandLinePtr, lStringLen
    End If
    
End Function

 

[Jerry Sullivan]

Hi Jaafar,

I think the OPs objective is that when this workbook (let's call it MyWorkbook.xlsm) is opened, the _Open event code determines whether the file was opened through the command line with the AUTO, or through double-clicking the icon in Windows Explorer.

If the OP starts an Excel session by opening another workbook, Book1.xlsx, then opens MyWorkbook.xlsm, how does the Open event determine which method was used to open that file?

If one were to use the approach of clearing "AUTO" from the command line, I believe it that would need to be cleared after Opening each workbook, not just MyWorkbook.xlsm. That could work however that seems more burdensome than storing a temporary flag when MyWorkbook.xlsm is opened by the command line.

 

[Jaafar Tribak]

If the OP starts an Excel session by opening another workbook, Book1.xlsx, then opens MyWorkbook.xlsm, how does the Open event determine which method was used to open that file?

Hi Jerry,

Unless I am missing something, If the OP starts an Excel session by opening another workbook, Book1.xlsx, then opens MyWorkbook.xlsm on the same excel session (ie: Not via the commandline) , then the commandline will not have the 'AUTO' param in it, therefore the Open event should determine how the workbook was opened (see my Workbook_Open event code).

If on the other hand the workbook MyWorkbook.xlsm is opened via the commandline, it will open in its own seperate session and the excel command line will have the param 'AUTO' in it .. This param will stay throughout the life of the excel session unless it is cleared upon closing the workbook via code.

Following are the user steps and scenario that I am visualising :

1- User opens workbook via the commandline passing the 'AUTO' switch param.
2- A new excel session is launched with the workbook loaded in it.
3- The Open event retrieves the 'AUTO' param and runs code accordingly.
4- After he is done working with the workbook, the user closes it. (Excel is left open)
5- Upon closing in step 4, the Before_Close event clears the 'AUTO' param from the excel Commandline string.
6- The user now Re-Opens the workbook by double-clicking the icon in Windows Explorer.. The workbook is loaded in the excel session that was left open in step 4.
7- The Open event code searches the commandline string for the 'AUTO' param but now it can't find it because it was already removed in step 5.

 

[Jerry Sullivan]

If on the other hand the workbook MyWorkbook.xlsm is opened via the commandline, it will open in its own seperate session and the excel command line will have the param 'AUTO' in it .

My comments were based on the possibility that the command line might not be set to open the MyWorkbook.xlsm in a new session of Excel. The default is to open a workbook in an existing application if one exists unless a command switch /x is used (at least for my 2016 version of Excel- perhaps that wasn't the case for earlier versions).

The OP hasn't posted the command line string, so we don't know if that switch is used. He could add that switch and use your suggested code, however he might prefer not to have multiple applications open to simplify interaction between workbooks.

If the user wants the command line to start a new Excel session, then I agree that your approach and code are simpler and better.