Determine whether scheduled tasks opened a workbook

[Steve_R]

I need a WB to auto_close after Scheduled Tasks opens it but stay open when a user opens it manually. Is there a way to determine via VBA when a WB was opened by tasks (or, looking at the problem from another angle, not opened by a user)?

 

[Jaafar Tribak]

I need a WB to auto_close after Scheduled Tasks opens it but stay open when a user opens it manually. Is there a way to determine via VBA when a WB was opened by tasks (or, looking at the problem from another angle, not opened by a user)?

Take a look at this thread specially @ my Post#5

 

[Steve_R]

Thanks. Hopefully this will work around my VBA WScript Popup issue. I will update the thread after thorough testing.

 

[Steve_R]

It's not working for me. Have I missed something?

Task is called on startup
Results: 8:01 was manually opened; 13:01 was a restart.

[TABLE="width: 283"]
<tbody>[TR]
[TD]Opened by Task Manager[/TD]
[TD]Opened By Tasks[/TD]
[/TR]
[TR]
[TD]6/08/2018 8:01[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6/08/2018 13:01[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]


Obviously, it's hanging on Application.Quit. I tried more often but didn't bother keeping other results that were locked by Excel not quitting.
Wit Excel hung AND before I start Excel AND with Excel not showing in Task Manager Applications or Processes, if I run some VBS to get CommandLine I am seeing

"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /dde

There is no reference to the call by Task Manager.


My code below.

Private Sub Workbook_Open()

    TasksOpen.HowOpened


End Sub

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)




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




Function WBOpenByTasks() As Boolean


    If InStr(1, GetCommLine, "TaskScheduler") Then
        WBOpenByTasks = True
    Else
        WBOpenByTasks = False
    End If


End Function




Function LastRow() As Long


    LastRow = Worksheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row


End Function






Sub HowOpened()
    Dim LR
    Dim RetVal
    Dim WB


    RetVal = WBOpenByTasks


    LR = LastRow + 1


    Worksheets("Sheet1").Cells(LR, 1) = Now
    Worksheets("Sheet1").Cells(LR, 2) = WBOpenByTasks


    If RetVal Then
        For Each WB In Workbooks
            WB.Save
        Next WB
        Application.Quit
    End If
    
End Sub

 

[Steve_R]

Oops! Short of information:
Or, when the account to run the Task is changed from my account to SYSTEM, I get NULL.

 

[Jaafar Tribak]

It looks like adding arguments to the commandline via task scheduler in new versions of Windows has changed ... Anyway, this worked for me in Windows 10 :

Let's assume the full name of the workbook is "C:\Test\MyBook.xlsm"

1- Open the Windows Task scheduler program and go to Create Task
2- Give a name to the new task under the General tab
3- Click on the Actions Tab and clcik on the New Button
4- Under Program/Scrip, browse for the Excel executable file (In my PC, it is "C:\Program Files\Microsoft Office\Office14\EXCEL.EXE")
5- Under Add Arguments field, write the following : C:\Test\MyBook.xlsm /TaskScheduler and click OK button
6- Set the time and periods you want task scheduler to run the workbook under the triggers tab and press OK - OK

Now in the ThisWorkbook Module of your workbook, add the following code :

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCommandLine Lib "kernel32" Alias "GetCommandLineA" () As Long
    Private Declare PtrSafe Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As LongPtr) As Long
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As LongPtr)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    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)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Sub Workbook_Open()

    If InStr(1, GetCommLine, "TaskScheduler") Then
        Debug.Print "Workbook opened by task scheduler"
        Me.Saved = True
        Application.Quit
    Else
        Debug.Print "Workbook opened otherwise"
    End If
    
End Sub

Private Function GetCommLine() As String
    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

Hope this helps.

 

[Steve_R]

I'm afraid not. However, the MessageBoxTimeout method is working in a test workbook so that should keep me up & running until the opportunity or necessity to put Windows onto a better size SSD arises (whichever comes first).

Thank you for your assistance

 

[nikio8]

can you create another wb that will do the auto update?

that way user can open the wb with no issues.
perhaps create log, on wb.open sheet.hidden.newRow( api get username and time); save.

 

[Jaafar Tribak]

I'm afraid not. However, the MessageBoxTimeout method is working in a test workbook so that should keep me up & running until the opportunity or necessity to put Windows onto a better size SSD arises (whichever comes first).

Thank you for your assistance

Did you follow the steps for adding the commandline argument "TaskScheduler" in the Windows Task scheduler program ?

When you run the code, what commandline string do you get ?

 

[Steve_R]

Did you follow the steps for adding the commandline argument "TaskScheduler" in the Windows Task scheduler program ?

When you run the code, what commandline string do you get ?

I am seeing nothing in the Command line.

Before debugging the scheduled tasks code on the the Win 7 machine, I will see how it goes on the Win 10 machine (also has problems with WScript Popup).

The plan is to get back and debug, trying to work out why I am seeing nothing (learning about the method at the same time) but first I want the WB running reliably from Task Scheduler so it reliably keeps history when I am absent. The self-closing message box does that. Final test of that is overnight tonight before coding it into the Workbook.