Scheduled Macros not running on correct day

[shy4x4]

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?

 

[tybaltlives]

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]

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]

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:

    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.

 

[shy4x4]

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.