How was the workbook opened ?

[Jaafar Tribak]

I have a Macro that runs upon opening a workbook but I only want the code to run if the workbook was opened by the Windows Task Scheduler.

Does Excel has a way of detecting how the workbook was launched ? ie via the User or via the task scheduler .

Thanks all.

 

[jkpieterse]

I'd add a commandline parameter to the version started by the task scheduler and use this code to decide if there are any arguments and act accordingly:

http://www.dailydoseofexcel.com/archives/2006/01/25/command-line-arguments-in-vba/

 

[Jaafar Tribak]

I'd add a commandline parameter to the version started by the task scheduler and use this code to decide if there are any arguments and act accordingly:

http://www.dailydoseofexcel.com/archives/2006/01/25/command-line-arguments-in-vba/

Thanks Jan for answering the question.

Unfortunately, using the code in the Autpen routine crashes the entire application.
I have tried a variation of the GetCommandLine API which doesn't crash but doesn't return any of the commandline arguments.

I'll carry on investigating the commandline arguments approach and post back with the results.

 

[Jaafar Tribak]

I can think of another solution and that is scheduling a small VBScript which opens a new excel instance and string tag the application so that the workbook can run the code according to whether the application is tagged or not.

However, the suggested Commandline approach looks much tidier.

 

[Jaafar Tribak]

Ok- the reason the application was crashing was because the GetCommandLine API returns a long Pointer to a String and not a String as per the link suggested. With this correction, the code now works beautifully.

here is the full commandline added to the Task Scheduler with the custom argument TaskScheduler argument added to it:

"C:\PROGRA~1\MICROS~2\Office12\EXCEL.EXE /TaskScheduler C:\Test.xlsm"

and here is the amended API code in the Workbook module :

Option Explicit

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 Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)

Private Sub Workbook_Open()

    If InStr(1, GetCommLine, "TaskScheduler") Then
        MsgBox "Workbook opened by task scheduler"
    Else
        MsgBox "Workbook opened otherwise"
    End If
    
End Sub

Private Function GetCommLine() As String

    'URL: http://www.allapi.net/
    Dim RetStr As Long, SLen As Long
    Dim Buffer As String
    
    RetStr = GetCommandLine
    SLen = lstrlen(RetStr)
    If SLen > 0 Then
        GetCommLine = Space$(SLen)
        CopyMemory ByVal GetCommLine, ByVal RetStr, SLen
    End If
    
End Function

Again , thanks for your help with this jkpieterse.

 

[Zardax]

Hey Jaafar, you can also use 'command$' to get the inputs.

 

[jkpieterse]

The command$ function is always empty for Office unfortunately.

 

[Jaafar Tribak]

The command$ function is always empty for Office unfortunately.

Tahanks for confirming this as i have never been able to make the VBA command$ work.

 

[Zardax]

Well that'd be a key difference between VB and VBA that this nuff nuff forgot about.

Looking through some old workbooks I had launching via tasks I used to check things like user name (we'd use a specific account for scheduled tasks) and also the launch path. Didn't have to worry about interpreting the command line then.