Assistance with a routine to be triggered based on specific days and time of the week

Luthius

Active Member
Joined
Apr 5, 2011
Messages
271
Guys
I developed a spreadsheet that when the file is opened it sends an e-mail after 1 minute and then it auto close the workbook.
Code:
Application.OnTime Now + TimeValue("00:01:00"), "SendReport"
There are certain days that I need to send the report. So I created a task scheduled on Windows to send the report every
Wednesday, Friday, Saturday at 12:00:30 and on Sundays at 07:15:30.

Sometimes I will need to use the file for update some information inside or maybe manipulate some data as well.
I need to find a way to open this file and edit it without I send every minute an email with the report.
My Idea is to develop a routine that can verify if the Time and Day are different from what I have on Task Scheduler so the event Workbook_Open() will not send any email.

Below my general ideia. I need your assistance guys on how to make it works properly.

Code:
Private Sub Workbook_Open()    
    If TriggerVerifier Then
        Application.OnTime Now + TimeValue("00:01:00"), "SendReport"
    Else: End If
End Sub
Code:
Function TriggerVerifier() As Boolean
   
    TriggerVerifier = True
    Select Case Weekday(Date, 1)
    Case 1
        If Time < TimeValue("07:15:30") Or Time > TimeValue("09:46:45") Then
            TriggerVerifier = False
        End If
    Case 4, 6, 7
        If Time < TimeValue("12:00:30") Or Time > TimeValue("12:01:45") Then
            TriggerVerifier = False
        End If
    Case 2, 3, 5
            TriggerVerifier = False
    End Select
End Function
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
271
The routine is that one. But I'm not sure if this is the best solution. Anyone can assist on this please?
 
Last edited:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,949
We can use Windows API functions to read the Excel command line, and the Workbook_Open can take different actions depending on whether the workbook was opened by the Task Scheduler or not.

Put this code in a new standard module:
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 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)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function w_commandline Lib "kernel32.dll" Alias "GetCommandLineW" () As Long
    Private Declare Function w_strlen Lib "kernel32.dll" Alias "lstrlenW" (ByVal lpString As Long) As Long
    Private Declare Sub w_memcpy Lib "kernel32.dll" Alias "RtlMoveMemory" (dst As Any, src As Any, ByVal size As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Public Function GetCommandLine() As String
    GetCommandLine = String$(w_strlen(w_commandline()), 0)
    w_memcpy ByVal StrPtr(GetCommandLine), ByVal w_commandline(), LenB(GetCommandLine)
    Debug.Print ">" & GetCommandLine & "<"
End Function
Change your Workbook_Open event handler to:
Code:
Private Sub Workbook_Open()
    If InStr(1, GetCommandLine, "TaskScheduler") Then
        'The Excel command line contains "TaskScheduler"
        Application.OnTime Now + TimeValue("00:01:00"), "SendReport"
    End If
End Sub
Next, in Task Scheduler create a Basic Task with the following Actions:

Start a program:
"C:\folder\path\to\EXCEL.EXE"

Arguments:
/x "C:\folder\path\to\Macro Workbook.xlsm" /TaskScheduler

Define the Triggers (scheduled times) as required.

The program path must be the full path to EXCEL.EXE, e.g. "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE" and the Arguments setting must contain the full path to your macro workbook. Double quotes are needed where shown. The /x is an Excel command line switch which starts a new instance (a separate process) of Excel, preserving the specified command line if Excel is already open.
 

Forum statistics

Threads
1,078,134
Messages
5,338,428
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top