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!
1. Create Auto_Open workbook
2. Schedule Task
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.
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.
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
3. Task Scheduler
Under Start, search for Task Scheduler and open it.
Using the top menu, click "Action" and then "Create Task".
You only need to give it a name here - any will do.
There are lots of options here to play with. To make a task fire at a 11am every day, use these settings:
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:
Set the start time around 10 minutes from your current time.
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.
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.
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
Right click - Properties to edit your task, Edit the triggers to turn it off.
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.
Next step is to try and schedule emails to be sent every hour. Give it a go, the email macro is easy!
Download the Auto_Open template below!