Running a macro on time

A very common case in practice: you need to run one or more of your macros at a given time or at a certain frequency. For example, you have a large and heavy report that updates half an hour and you would like to run the update half an hour before you arrive at work in the morning. Or you have a macro that should automatically send out emails to employees at a specified frequency. Or, when working with a PivotTable, you want it to update on the fly every 10 seconds, and so on.

Let’s look at what Excel and Windows have the ability to implement this.

Running a macro at a given frequency

The easiest way to do this is to use the built-in VBA method Application.OnTimeAn that runs the specified macro at the specified time. Let’s understand this with a practical example.

Open the Visual Basic editor with the button of the same name on the tab developer (Developer) or keyboard shortcut Alt+F11, insert a new module through the menu Insert – Module and copy the following code there:

Dim TimeToRun 'global variable where the next run time is stored 'this is the main macro Sub MyMacro() Application.Calculate 'recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cell A1 with a random color :) Call NextRun 'run the NextRun macro to set the next run time End Sub 'this macro sets the time for the next run of the main macro Sub NextRun() TimeToRun = Now + TimeValue("00:00:03") 'add 3 seconds to the current time Application.OnTime TimeToRun, "MyMacro" 'schedule the next run End Sub 'macro to start the repeat sequence Sub Start() Call NextRun End Sub 'macro to stop the repeat sequence Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Let’s figure out what’s what here.

First, we need a variable that will store the time of the next run of our macro – I called it TimeToRun. Please note that the contents of this variable must be available to all our subsequent macros, so we need to make it global, i.e. declare at the very beginning of the module before the first Sub.

Next comes our main macro MyMacro, which will perform the main task – to recalculate the book using the method Application.Calculate. To make it clearer, I added the formula =TDATE() to the sheet in cell A1, which displays the date and time – when recalculated, its contents will be updated right before our eyes (just turn on the display of seconds in the cell format). For extra fun, I also added to the macro the command to fill cell A1 with a randomly selected color (the color code is an integer in the range 0..56, which is generated by the function Rnd and rounds up to an integer function Int).

Macro NextRun adds to the previous value TimeToRun 3 more seconds and then schedules the next run of the main macro MyMacro for this new time. Of course, in practice, you can use any other time intervals you need by setting the function arguments TimeValue in the format hh:mm:ss.

And finally, just for convenience, more sequence launch macros have been added. Home and its completion Finish. The last one uses the fourth method argument to break the sequence. On time equal False.

Total if you run the macro Home, then this whole carousel will spin, and we will see the following picture on the sheet:

You can stop the sequence by running, respectively, the macro Finish. For convenience, you can assign keyboard shortcuts to both macros using the command Macros – Options tab developer (Developer — Macros — Options).

Running a macro on a schedule

Of course, everything described above is possible only if you have Microsoft Excel running and our file is open in it. Now let’s look at a more complicated case: you need to run Excel according to a given schedule, for example, every day at 5:00, open a large and complex report in it and update all connections and queries in it so that it will be ready by the time we arrive at work 🙂

In such a situation, it is better to use Windows Scheduler – a program specially built into any version of Windows that can perform specified actions on a schedule. In fact, you are already using it without knowing it, because your PC regularly checks for updates, downloads new anti-virus databases, synchronizes cloud folders, etc. It’s all the work of the Scheduler. So our task is to add to the existing tasks another one that will launch Excel and open the specified file in it. And we will hang our macro on the event Workbook_Open this file – and the problem is solved.

I want to warn you right away that working with the Scheduler may require advanced user rights, so if you cannot find the commands and functions described below on your work computer in the office, contact your IT specialists for help.

Launching the Scheduler

So let’s start the Scheduler. To do this, you can either:

• Right click on the button Start and choose Computer Management (Computer management)
• Select in Control Panel: Administration – Task Scheduler (Control Panel — Administrative Tools — Task Scheduler)
• Select from the main menu Start – Accessories – System Tools – Task Scheduler
• Press keyboard shortcut Win+R, enter taskschd.msc and press Enter

The following window should appear on the screen (I have an English version, but you can also have a version):

Create a task

To create a new task using a simple step-by-step wizard, click on the link Create a simple task (Create Basic Task) in the right panel.

At the first step of the wizard, enter the name and description of the task to be created:

Click on the button Next (Next) and in the next step we select a trigger – the launch frequency or an event that will launch our task (for example, turning on the computer):

If you chose Daily (Daily), then in the next step you will need to select a specific time, start date of the sequence and step (every 2nd day, 5th day, etc.):

The next step is to choose an action – Run the program (Start a program):

And finally, the most interesting thing is what exactly needs to be opened:

In the Program or script (Program/script) you need to enter the path to Microsoft Excel as a program, i.e. directly to the Excel executable. On different computers with different versions of Windows and Office, this file may be in different folders, so here are a few ways for you to find out its location:

• Right-click on the icon (shortcut) to launch Excel on the desktop or in the taskbar and select the command Materials (Properties), and then in the window that opens, copy the path from the line Target:

                       

• Open any Excel workbook, then open Task Manager (Task Manager) pushing Ctrl+Alt+From and by right clicking on the line Microsoft Excel, choose a command Materials (Properties). In the window that opens, you can copy the path, not forgetting to add a backslash to it and EXCEL.EXE at the end:

               

• Open Excel, open Visual Basic editor with keyboard shortcut Alt+F11, open panel immediate a combination of Ctrl+G, enter the command into it:

  ? Application.Path

  … and click on Enter

  

  Copy the resulting path, not forgetting to add a backslash to it and EXCEL.EXE at the end.

In the Add arguments (optional) (Add arguments (optional)) you need to insert the full path to the book with the macro that we want to open.

When everything is entered, then click Next and then Finish (Finish). The task should be added to the general list:

It is convenient to manage the created task using the buttons on the right. Here you can test the task by running it immediately (run)without waiting for the specified time. You can temporarily deactivate a task (Disable)so that it stops running for a period of time, such as your vacation. Well, you can always change the parameters (dates, time, file name) through the button Materials (Properties).

Add a macro to open a file

Now it remains to hang in our book the launch of the macro we need on the file open event. To do this, open the book and go to the Visual Basic editor using the keyboard shortcut Alt+F11 or buttons Visual Basic tab developer (Developer). In the window that opens in the upper left corner, you need to find our file on the tree and double-click to open the module This book (ThisWorkbook).

If you do not see this window in the Visual Basic editor, then you can open it through the menu View — Project Explorer.

In the module window that opens, add a book open event handler by selecting it from the drop-down lists at the top Workbook и Open, respectively:

A procedure template should appear on the screen. Workbook_Open, where between the lines Private Sub и End Sub and you need to insert those VBA commands that should be automatically executed when this Excel workbook is opened, when the Scheduler opens it according to the schedule. Here are some useful options for overclocking:

• ThisWorkbook.RefreshAll – Refreshes all external data queries, Power Query queries, and PivotTables. The most versatile option. Just don’t forget to allow connections to external data by default and update links via File – Options – Trust Center – Trust Center Options – External Content, otherwise, when you open the book, a standard warning will appear and Excel, without updating anything, will wait for your blessing in the form of clicking on the button Enable content (Enable content):

  

• ActiveWorkbook.Connections(“Connection_Name”).Refresh — updating data on the Connection_Name connection.
• Sheets(“Sheet5“).PivotTables(“PivotTable1«).PivotCache.Refresh – updating a single pivot table named PivotTable1 on the sheet Sheet5.
• Application.Calculate – recalculation of all open Excel workbooks.
• Application.CalculateFullRebuild – forced recalculation of all formulas and rebuilding of all dependencies between cells in all open workbooks (equivalent to re-entering all formulas).
• Worksheets(“Report”).PrintOut – print sheet Photos.
• Call MyMacro – run a macro named MyMacro.
• ThisWorkbook.Save – save the current book
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – save the book to a folder D:Archive under the name Photos with date and time appended to the name.

If you want the macro to be executed only when the file is opened by the Scheduler at 5:00 am, and not every time the user opens the workbook during the working day, then it makes sense to add a time check, for example:

If Format(Now, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll  

That’s all. Don’t forget to save your workbook in a macro-enabled format (xlsm or xlsb) and you can safely close Excel and go home, leaving your computer on. At a given moment (even if the PC is locked), the Scheduler will launch Excel and open the specified file in it, and our macro will perform the programmed actions. And you will luxuriate in bed while your heavy report is automatically recalculated – beauty! 🙂

• What are macros, how to use them, where to insert Visual Basic code in Excel
• How to create your own macro add-in for Excel
• How to use the Personal Macro Workbook as a library for your macros in Excel