Schedule Excel to run a macro at a specific time and date

SheriV · January 27th, 2009, 01:22 PM

I know how to schedule a macro to run at a specific time but is there a way to schedule to run at a specific time on a specific date? I have something that I need to occur on the 1st of each month at 5:00 AM. Not sure how to accomplish this.

Shasur · January 29th, 2009, 06:09 AM

There is no direct way in VBA to schedule a Macro for a specific date. You can use Application.OnTime to trigger a macro at specifed time.

You can either create an Exe and add it to Windows scheduler or Check for date in your macro and call the macro by resetting Application.OnTime

Code:

Sub MyProc()
If Day(Now) = 1 Then
    
    ' Execute the proc
End If
' extend procedure schedule by a day
 Application.OnTime Now() + TimeValue("24:00:00"), "MyProc"
End Sub

Cheers
Shasur

SheriV · January 29th, 2009, 01:49 PM

Thanks for the help Shasur.

JP · February 5th, 2009, 10:31 PM

You could use the technique found here:

Running Excel on Windows Task Scheduler

Or set up Task Scheduler to open a specific workbook at that time. Put your macro code in the workbook's Workbook_Open() event.

Or if you have Outlook running, you can adapt the code found here to automatically open and run an Excel macro:

Handling Multiple Inboxes

For example, if you set up a task reminder in Outlook to trigger at the time you want, you can set up an event handler in Outlook to open the Excel workbook and run the appropriate macro at that time.

HTH

Quote:

Originally Posted by SheriV

I know how to schedule a macro to run at a specific time but is there a way to schedule to run at a specific time on a specific date? I have something that I need to occur on the 1st of each month at 5:00 AM. Not sure how to accomplish this.

SheriV · March 18th, 2009, 04:12 PM

Thanks for the info JP.