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:
- 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.
- 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.
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!
Hi I ha the same 3 options in mind when I read your intersting and detailed blog.
I really need to use the third option because I dont want the code to be executed when I open the file.
(for maintaining it for example).
I’ve got an issues with the VBS + VBA solution,
– if you put in a msgbox inthe vba code for debuggin purposes the message pop out twice
– stop command is ignored
– the debugger itself doesnt seem to launch (tried with the wscript /d flag doesnt change anything). and an forced crash (
Dim MyIntVarCrash as int
MyIntVarCrash = 1 / 0
stops the VBA program but not lauching anything
have you encountered this type of problem and if so, have you found any workaround ?
Thanks
Polo.
Hi Polo.
I’m not sure I completely understand your situation, but what comes to mind:
Why use msgbox for debugging, especially in a file to be executed by a VBS script? Use Debug.Print to throw data to the immediate window.
Also, if you want to have different behavior when run by VBS vs when opened and run manually, have a boolean variable to flag the env (e.g. dev/prd). See here: https://morsagmon.com/blog/the-full-dev-to-qa-to-prod-cycle-with-excel-vba-projects/
(In your code, do I see one being divided by 0?).
Hope that helps!