Schedule Macros - Any Date Or Time!

Recurring or one off schedules are all possible and easy to do with Task Scheduler. This makes forgettable tasks like backups/auto-reporting a breeze!

Updated Jan 2021

 

Steps

1. Create Auto_Open workbook

2. Schedule Task

3. Done

 

 

Watch a 3 minute tutorial video, or read on and download below!

1. The Objective

I started looking for this when I wanted to send automated daily emails using Excel. The automated emailing is all possible using macros, but the problem was getting the macro to automatically run at a certain time, recurring daily. I also needed it to start Excel if it was closed, and remember even if the computer had been shut down. 

Researching online led me to Task Scheduler, which more than met my requirements. The below will be part of my Auto Backup tutorial and Auto Email tutorial. 

 

2. Building The Sheet

1. Create launcher workbook.
This is going to be what Task Scheduler opens, so make sure it is .xlsm
Alt+F11 to bring up the VBA editor. Right click on "ThisWorkbook", click "Insert", click "Module".
Double click on "Module 1". Copy in the code below: 

Sub Auto_Open()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

MsgBox "Excellen" 'Replace this with your macro code!


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

The only difference to any normal piece of VBA is the Sub Auto_Open( ). You usually have Sub MacroName( ).
This just runs the macro upon opening the workbook as opposed to manually running it - perfect for things like refreshing pivot tables!

2. Save the workbook
Use an easy name, as you need it for Task Scheduler. Easier if no spaces - "BackupTestSheet.xlsm"
Exit out of Excel, and open the workbook to verify the Auto Run macro is working, you should simply get a message box pop up. 

Autopopup.png

 You might have macros blocked by default, which means you need to constantly push the allow button before you see the message box. 
File - Options - Trust Center - Trust Center Settings - Macro Settings - Enable all macros 

trust center.png

3. Task Scheduler
Under Start, search for Task Scheduler and open it.
Using the top menu, click "Action" and then "Create Task". 

Start Search Task Schedular.png

General Tab
You only need to give it a name here - any will do.

Triggers Tab
Click "New...:
There are lots of options here to play with. To make a task fire at a 11am every day, use these settings:

Once every day macro.png

If you wanted it Weekly/Monthly you simply select the circles in the top left. 

However for this test, I want it firing every 5 minutes. Change the settings to the below:

Repeat 5minutes.png

Set the start time around 10 minutes from your current time. 
Click OK

Actions Tab
Click "New...:
Action should be "Start a program". 
"Program/script:" needs to point at your EXCEL.EXE. Mine is "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE" but be sure to verify where your one is. 
Copy it in, quotes and all. 

"Add arguments (optional):" needs to point to your excel workbook created above. Mine is "C:\Users\profilexx\Desktop\Macros\BackupTestSheet.xlsm"
That's it, click OK.

New Action.png

Settings Tab
In the very bottom drop down box, set "Run a new instance in parallel". If you don't do this, it won't work if Excel is already open. 
It can also be worthwhile checking the "Run task as soon as possible after a scheduled start is missed". That way, if the computer was off during the start it will run as soon as you turn it on. 

Settings Tab.png

 

Click OK out of Settings and OK again. That's it! Task scheduled! You can see the details by clicking on "Task Scheduler Library" and finding your task

Macro Details.png

Right click - Properties to edit your task, Edit the triggers to turn it off. 

3. Summary

The message box should now popup every 5 minutes.


With this setup, it won't recur if the BackupTestSheet is open. Add ActiveWindow.Close to the end of the VBA code if you want your workbook to automatically close after running. Just open the workbook in protected mode if you want to edit it after doing that - else the Auto_Open macro will keep on closing it! 
If you run into problems, redo the Action Tab as that is the most likely culprit. 


This opens up a lot of interesting possibilities - auto backups, auto emails, auto PDF generation, reminders, info popups, monthly reports. If you happen to have a virtual PC on a server, you can even schedule macros without worrying about your computer being shut down, or use Google Sheets to automate emails /scripts, then automatically link Google Sheets to Excel. With tiny tweaks, this also allows you to schedule Python and Bat files.

Next step is to try and schedule emails to be sent every hour. Give it a go, the email macro is easy!

 If you have problems, leave a comment here and I will help!

Download the Auto_Open template below!

4. Download

More templates