FOLLOW

CONTACT

ADDRESS

Pleasant Grove, UT

©2017 by Office Newb

UdemyNewYearPromo.jpg
PaparazziAd-01.jpg

Schedule Microsoft Excel Tasks with Windows Task Scheduler

September 15, 2017

I got an excellent question from a student in my Excel course hosted on Udemy.

 

QUESTION:

 

"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.

 

GENERIC SCENARIO:

 

Imagine you have a daily task that involves work within Microsoft Excel. Below are a few examples to get you thinking.

  • Refreshing imported data

  • Generating a daily report

  • Sending an email based on Excel data

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

  1. 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

  2. OPEN Windows Task Scheduler

  3. Under the ACTION MENU -- Select CREATE BASIC TASK...

     

  4. There are four steps to completing a task

    1. Name/Describe the Task -- Press Next

    2. Specify what TRIGGERS the Task (Daily, Weekly, Monthly, etc...) -- Press Next

    3. Specify ACTION (START A PROGRAM) -- PRESS NEXT

    4. FINISH

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

 

 

 

 

 

 

 

Please reload

Recent Posts

Please reload

Archive

Please reload

Tags

Please reload