Auto run a macro on a specified date

[Rupert Bennett]

I would like to run a macro on Dec 31st each year. the workbook will be opened, but not necessarily being used on that day so I would like the macro to run by itself. I can code what functions need to be performed. I just do not know how to write the code for the date to be recognized and then do the tasks. Can anyone help with this, if it is possible and also say what module I would place the code in?
Your help will be greatly appreciated.
Rupert

 

[parry]

Hi chiello, unfortunately your procedure will not complete the task if the book isnt opened on the 31/12. eg book is opened on 30/12 and then next on 1/1 - the procedure will never run. Thats why you need to check if today is > a target date and then after running the macro alter the target date forward so its only run when its next due rather than multiple times.

regards
Parry

 

[chiello]

Ciao parry, what you say is exact at all

So I have updated my solution. Is it compliant with Rupert'request?

Copy in Thisworkbook code window the routine below:

Private Sub Workbook_Open()
Call TestDayOfYear
End Sub

and in a Module code window what follows:

Public Sub TestDayOfYear()
Dim MyDay As Long, MyMonth As Long
Dim CheckDate As Date, RunIN As Date
'Data acquisition
CheckDate = Now
MyDay = Day(CheckDate)
MyMonth = Month(CheckDate)
'Check of Day of the Year
If MyDay = 31 And MyMonth = 12 Then
'Specify time to last from opening to execution (15 seconds in this example)
RunIN = TimeValue("00:00:15")
Application.OnTime Now + RunIN, "my_Procedure" 'change the name of the macro being called.
Else
'Try again later, e.g. 12 hours
RunIN = TimeValue("12:00:00")
Application.OnTime Now + RunIN, "TestDayOfYear" 'Recursive call
End If
End Sub

Ciao

 

[parry]

Ciao chiello.

Its certainly an interesting approach to use the OnTime method and would work as long as the workbook remains open. Unfortunately the OnTime method loses its 'memory' of when to run a procedure next when Excel is closed.

Even though it may not be perfect its certainly a clever way to solve the problem.

arrivederci,
Parry

 

[chiello]

Sorry Perry, I don't understand...

If I close Excel, I can't run the Macro anyway.
If I open It again, I think the code in Workbook_Open routine 'restores Memory' to the OnTime. Where do I fail?

Thanks for your answer..

 

[parry]

Hi, the problems are
1) Its still on running the my_Procedure macro on 31/12 so unless the book is opened on that day you still have problems. I believe you actually need to store a date or else you will have problems with it running multiple times or not at all.

If I open It again, I think the code in Workbook_Open routine 'restores Memory' to the OnTime. Where do I fail?
2) Yes your right, I didnt think it would. I ran an OnTime macro closed the book then immediately re-opened it and it ran as scheduled. I wonder if it would still work after rebooting though. However, if the book isnt open at that time it wont run.