Ever needed to run an Excel Macro every day automatically? I mean, without leaving Excel open or interacting with Excel at all. Here’s how you do this taking advantage of two technologies that comes with Windows

Why schedule a daily silent job?

In one of my customer’s project that manages trainings and certifications, employees receive email and SMS notifications on various events: upcoming training reminders, training result (with a certificate attached), change of instructor, training assessment reminders etc.

The technique for doing that is using a queue. All notifications requests accumulate in a queue in response to daily activities by the users, and once a day (for example) the queue is attended by an automatic process (an “agent”) to clear all pending notifications: send the notification, update its status, etc. I maintain this queue in a dedicated table in the Database (MySQL, in this project) serving all Excel clients of the application.

This “agent” is not to require any human intervention, but rather silently wake up once a day on some computer, run its job, and vanish.

Another example would be a daily (or weekly) report that needs to be automatically generated, archived and sent as an attachment by Email to relevant managers.

Excel VBA as a silent agent

In order to silently execute an Excel VBA Sub, three approaches are considered:

Windows Task Scheduler calling the Excel file directly

This is a straight forward way, by which you call your VBA Sub to run upon the Workbook open event (Workbook_Open sub in the Workbook module) and add a task in the Windows Task Scheduler that calls an application. You point the task to the Excel Workbook directly.

This will work, however, the Task Scheduler does not close the Excel Workbook (as it has no control over its execution thus cannot know when it has finished).

One way to handle this would be of course to close the Workbook at the end of the required tasks using, for example, these two VBA commands:

ThisWorkbook.Saved=True

Application.Quit

Windows Task Scheduler calling a VBScript that opens the Excel file

Here, instead of having the Task Scheduler call the Excel file directly, we call a VBScript that in turn calls to open the Excel Workbook. Running our VBA code upon opening and closing the application still remains the responsibility of the Excel Workbook itself.

We take advantage of the built-in Windows VBScript engine (that as you’ll see resembles VBA very much). VBScript has access to external objects (just like Excel VBA has, as I demonstrated in this Blog post), therefore it can open any Excel file. A VBScript file has the vbs extension.

Using a VBScript, we have some flexibility of performing additional tasks, outside of Excel (such as manipulating other files and folders), leaving only the Excel-related tasks for the Excel VBA code.

Another advantage of using VBScript as an Excel file launcher is the fact that the VBScript file can be launched from a remote machine on the network on which Office is not even installed. You can have a thin machine launching automatic tasks on other machines in the network.

Here’s the code of a typical VBScript file that launches an Excel file:

scriptdir = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open scriptdir & "\SayHello.xlsm"

objExcel.DisplayAlerts = False

objExcel.Application.Quit

Set objExcel = Nothing

As you can see, this script will look for the Excel file to open in the same folder where the VBScript file itself resides.

I’m using Windows’ FileSystemObject library for convenient files and folders services, and of course the Excel application library to open the Excel file.

Before closing the application, I’m turning alerts off to avoid a popup asking to save changes. Remember, the whole idea here is to have a silent running service with no human intervention.

Windows Task Scheduler calling a VBScript that runs an Excel VBA Sub

The last method I’ll describe is similar to the previous one, only instead of the VBScript opening an Excel Workbook, it actually calls to execute a specific Sub in an Excel Workbook.

Here, we are not using the Workbook’s Open event, thus the Workbook can also be opened manually at anytime without triggering the daily task Sub (if only for maintenance).

Here’s the code for that:

scriptdir = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)

Set objExcel = CreateObject("Excel.Application")

objExcel.Application.Run "'" & scriptdir & "\SayHello.xlsm'!Hello.HelloWorld"

objExcel.DisplayAlerts = False

objExcel.Application.Quit

Set objExcel = Nothing

HelloWorld is a VBA Sub, located in the Hello module, which part of the VBA project within the SayHello.xlsm file.

All we need to do is to save this text file as a VBScript file (let’s say: SayHelloLauncher.vbs) and schedule a daily task in Windows’ Task Scheduler to say Hello every morning!

Clearing Security for Uninterrupted Execution

Depending on the security policy in your organization, and Excel settings, it may be that Excel will prompt to approve running the VBA code as the Workbook is opened. If that happens, it breaks the smooth, uninterrupted execution of our daily tasks, and we don’t want that.

In order to secure uninterrupted execution, you have two options:

1. Assign a certificate to the Excel file (see a straightforward guide here). If other Excel files are to be executed on this machine – associate the certificate with every one of them.
2. Relax the Excel security settings (well explained here). As this allows any VBA code to run without any warning, use this only if you are sure Excel on that machine will not be required to open and run Excel files from un-trusted sources.