I got an excellent question from a student in my Excel course hosted on Udemy.
"How can you schedule Microsoft Excel automation?"
I've paraphrased the question a little. The student has a specific task that needs to be schedule for daily output. But, in writing this post I want to make it a little more generic as this concept can be applied to loads of scenarios.
Imagine you have a daily task that involves work within Microsoft Excel. Below are a few examples to get you thinking.
The above are only a few examples of what might be considered an Excel daily task. Now, imagine this task or tasks need to be performed each day at a specific time of the day. Your first thoughts might turn to Excel macros/VBA. And, you would be part of the way there.
EXCEL MACROS/VBA LIMITATIONS:
The ability to create automation within an Excel file using macros/VBA is awesome! If you can do it within Excel, and in some cases outside of Excel, it can be automated with a little VBA magic. But, there are some limitations that we need to be aware of as well.
Specifically, in regard to automating a scheduled task, Microsoft Excel VBA has a big limitation. In order for a VBA procedure to run Excel needs to be open, or the file that contains the VBA code needs to be open. If you can guarantee that the code will be accessible at the time when the procedure is scheduled then this may not be a problem.
OVERCOMING THIS LIMITATION
Installed on a Microsoft Windows machine is a utility called Windows Task Scheduler. Using the Task Scheduler allows user to create simple automated tasks, such as opening a Microsoft Excel document at a specific time of day.
Image of Windows Task Scheduler
The Windows Task Scheduler can be located on a Windows machine by searching for "Task Scheduler" from the start menu.
STEPS TO SCHEDULE OPENING AN EXCEL FILE
Create a Microsoft Excel file
(OPTIONAL) Create an Excel VBA Procedure and place in the OPEN EVENT of the WORKBOOK
By placing a procedure in the OPEN EVENT of the WORKBOOK you can automate a task when the Workbook is opened by the Task Scheduler
OPEN Windows Task Scheduler
Under the ACTION MENU -- Select CREATE BASIC TASK...
There are four steps to completing a task
Name/Describe the Task -- Press Next
Specify what TRIGGERS the Task (Daily, Weekly, Monthly, etc...) -- Press Next
Specify ACTION (START A PROGRAM) -- PRESS NEXT
By combing both Microsoft Excel and Windows Task Scheduler you can automate routine tasks completely hands free.
HOW TO USE TASK SCHEDULER
HOW TO WRITE A PROCEDURE ON THE OPEN EVENT OF A WORKBOOK