Scheduled Macros not running on correct day

shy4x4

New Member
Joined
Oct 11, 2005
Messages
34
Hi all,

I have an issue with some macros I have scheduled to run on various days of the week. I have the below code placed in “ThisWorkbook” of the macro workbook.

Private Sub Workbook_Open()
'Monday
If Weekday(Now()) = vbMonday Then
Application.OnTime TimeValue("23:44:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
End If
If Weekday(Now()) = vbMonday Then
Application.OnTime TimeValue("23:45:00"), "ArchiveBatFile.ArchiveBatFile"
End If
If Weekday(Now()) = vbMonday Then
Application.OnTime TimeValue("23:46:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
End If

'Tuesday
If Weekday(Now()) = vbTuesday Then
Application.OnTime TimeValue("01:59:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
End If
If Weekday(Now()) = vbTuesday Then
Application.OnTime TimeValue("02:00:00"), "HelloThereBatFile.HelloThereBatFile"
End If
If Weekday(Now()) = vbTuesday Then
Application.OnTime TimeValue("02:01:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
End If

As you can see on Monday at 11:45pm the ArchiveBatFile macro is scheduled to run and this macro does run correctly at the scheduled time on Monday nights.

However, the macro HelloThereBatFile which is scheduled to run on Tuesday morning at 2am doesn’t run on Tuesday morning. For some reason it ran on Wednesday morning at 2am!

Does anyone know why is this happening?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
Could it have taken longer than 1 minuted to "CloseAllWorkbooks"?

If that was true, then by the time it got to the next weekday(Now()) statement the timevalue would be greater than 2:00. so it wouldnt run the batch file.

Just a guess. if true you might add 5 min to all your times to ensure the files close before the next IF statement.

-R
 

shy4x4

New Member
Joined
Oct 11, 2005
Messages
34
I can't see how "CloseAllWorkbooks" would take longer than 1 minute to complete only because I also have a batch file which closes Excel about a hour later. I also use Excel throught out the day in my day to day work. I would think that when you close Excel down, everything (including anything running in the background of Excel) is closed down with it?

However, I have not ruled it out as a possibility so I have removed all instances of "CloseAllWorkbooks" from my code. Will see how it goes.

I was thinking maybe something is up with our system time at work but I do not have access to change the day/time here. When I hover my mouse over the time it displays the correct day and date. We are a huge organisation so would not be granted access to change the time on my machines to test this theory further unfortunatley.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
However, the macro HelloThereBatFile which is scheduled to run on Tuesday morning at 2am doesn’t run on Tuesday morning. For some reason it ran on Wednesday morning at 2am!

Does anyone know why is this happening?
Because OnTime requires a date/time in the future and you are only specifying a time (without date) for the 'Tuesday' procedures and you are presumably opening the workbook after 2am on Tuesday. If you open the workbook before 2am on Tuesday then the 'Tuesday' procedures would run. Though not documented in the VBA Help, it seems that if you specify only a time, the OnTime procedure is run at the next time which matches the specified time, hence why they run on Wednesday.

One solution is to specify the date and time for the 'Tuesday' procedures:
Code:
    If Weekday(Now) = vbMonday Then
        If Now < TimeValue("23:44:00") Then
            'Schedule Monday procedures
            Application.OnTime TimeValue("23:44:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
            Application.OnTime TimeValue("23:45:00"), "ArchiveBatFile.ArchiveBatFile"
            Application.OnTime TimeValue("23:46:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
       End If
       
        'Schedule Tuesday procedures
        Application.OnTime Date + 1 + TimeValue("01:59:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
        Application.OnTime Date + 1 + TimeValue("02:00:00"), "HelloThereBatFile.HelloThereBatFile"
        Application.OnTime Date + 1 + TimeValue("02:01:00"), "CloseAllWorkbooks.CloseAllWorkbooks"
   End If
When opened on Monday, this workbook (or any Excel workbook) would have to remain open until Tuesday 2am in order for the Tuesday procedures to run.
 
Last edited:

shy4x4

New Member
Joined
Oct 11, 2005
Messages
34
Thanks for the info John. I have had a think about what you have said and have now written batch files which close Excel down after the macros have run. Batch Files then reopen Excel on the day the macros need to run so hopefully this fixes my issue. I think with what you said and having Excel stay open from Monday into Tuesday with my existing code was causing the issue and I needed Excel to Close down and reopen on the next day to have them work (without changing my existing code).

I will have to wait until next Monday now to retest my theory as thats when the next macros are scheduled to run.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,552
Messages
5,636,969
Members
416,953
Latest member
broexc

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
Top