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

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
The routine is that one. But I'm not sure if this is the best solution. Anyone can assist on this please?
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top