Is it possible to run a macro every day at a time?

I need to know if it is possible to have a macro executed daily at 7:00 am.

Would this be done through a macro, or through some visual basic component?

Workbook_Open()

2 Answers
2

There are a couple ways that I know of to do this, neither of which are perfect.

The first is the Application.OnTime method in VBA (documentation here). This method will schedule another method to be run at a specific time. I haven't thoroughly testing this function, so I don't know if the schedule will be preserved across excel restarts or even workbook closures.

Application.OnTime

Another option is the Windows Task Scheduler(thanks Mistella). You can set up a basic task within the task scheduler to open a specific workbook in excel at a given time at a given interval and have the code you wish to be run within the Workbook's Open event. Within that event you could check the time. If the time is close enough to your designated task scheduler time you could perform the work you needed to do, save the workbook, and close it, all without user interaction.

Open

The issue with the Workbook_Open() event is that if you still need to action on your workbook manually during the day, the macro will launch, which can be source of errors depending on what your macro does.

Workbook_Open()

Alternatively, you can use the Windows Task Scheduler as mentionned above to run a VB Script that will run the macro in your workbook.

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False 'turn off alerts
xlApp.Application.Visible = False 'keeps Excel hidden
Set xlBook = xlApp.Workbooks.Open("C:MyPathMyWorkbook.xlsm", 0, False)
xlApp.Run "MACRO_NAME"
xlBook.Save
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing


Just type your script within notepad and save as .vbs (and then have the Windows Task Scheduler to run you .vbs).

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.