Run Excel VBA via Windows Scheduler
Occasionally I am asked to build something in Excel that can refresh itself, reload data, export fresh data, upload data, and a host of other Excel tasks... periodically based on some schedule. Now, in bigger shops and large firms, there are probably computing assets where an application can be written that will run as a job on some server based on a schedule. But if you're in a small office with just a few users, this is easily accomplished with one of the users' desktops (or a spare workstation).
There are some requirements:
- The scheduling computer must be on. If this is a network application, the user must be logged in and connected when the scheduled time comes around.
- The computer running the schedule cannot be in Sleep mode (fully on, but locked is ok).
- The user must be logged into their Windows and network account. This sometimes is more of an issue on an overnight schedule if the workstation times out and drops off the network after some period of idle time.
An example task would be to create an Excel VBA script and set the workstation to run this script (macro) every night at 8:00 pm. So let's get into it.
The workflow is this:
1. Windows Task Schedule runs batch file at a given time (8pm) then...
2. Batch file (.bat) runs Windows VBS script (.vbs) then...
3. Windows VBS script opens Excel and asks Excel to execute VBA subroutine in the ThisWorkbook object code section then...
4. Windows VBS script closes Excel file (and saves if that's what is needed in the task)
Keeping the workstation alive
For most users, messing around with the power settings in Windows will keep the workstation from going to sleep. But if you are in a shop with an enterprise power policy, you can ask the IT department to exclude your computer from the policy OR... you can use Caffeine, a small exe program that tricks your computer into thinking you are still sitting there. It works by simulating a keypress once every 59 seconds, so it won't lock the screen or activate the screensaver. You can download Caffeine here. Just install it and run it. A small coffee pot icon will appear in the task manager to tell you it is working. Once running, the user account will stay logged in and fully active all night.
Writing the VBA script or Macro
I've conducted quite a few experiments in this regard (getting the script to fire correctly). While it seems that you can place your code in any application object you want, I have honestly had the most success in making sure that my VBA code subroutine is written in the "ThisWorkbook" object. It seems to be the most reliable - fires everytime. In this example, my code clears sheets and re-exports fresh data from the ERP system. My VBA sub is called "ClearThenLoad()" Do NOT create your VBA script in the WorkbookOpen() event - it will always run and you will find it very difficult to debug and get right.
Writing the Windows VBS script file (.vbs)
There's not much to show here. If you follow the comments below, you can easily see how this works... Save the file anywhere a batch file can easily get to it. In this example I saved the VBS script in my desktop folder. My .vbs file here is named COREDATALIB.vbs
'Create an instance of Excel
Set ExcelApp = CreateObject("Excel.Application")
'Do you want this Excel instance to be visible?
ExcelApp.Visible = True 'or False
'Input Excel File's Full Path
ExcelFilePath = "
H:\MyFolder\CORE_DATA.xlsm"
'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
'Input Module/Macro name within the Excel File
MacroPath = "ThisWorkbook.ClearThenLoad"
'Prevent any App Launch Alerts (like "Update External Links")
ExcelApp.DisplayAlerts = False
'Execute Macro Code
ExcelApp.Run MacroPath
'Save Excel File (if applicable)
wb.Save
'Reset Display Alerts Before Closing (optional)
ExcelApp.DisplayAlerts = True
'Close Excel File
wb.Close
'End instance of Excel
ExcelApp.Quit
Firing the Windows VBS script file from a Windows batch file (.bat)
This is basically a one-liner. The fact is, I've had much more luck launching a BAT file from the Windows Scheduler over launching the VBS file directly. I found launching the VBS file to be spotty. Some nights it didn't run, but the BAT file launches every time.
CoreDataLaunch.bat file is one line... C:UsersjmorgaDocumentsCoreDataLaunch.bat
Once the /bat file is written, you can just run it as a test. Everything should work up to this point as a manual launch. The only task left is to schedule the batch file to run when you want. In this example.... 8pm.
Building the Launch Schedule
This is the last portion of the build for which we will load the Windows Task Scheduler. Navigate to and open the Task Scheduler (just type "sched" in the app search bar and it should pop up).
In the Task Scheduler Actions List, click Create Task..,
Now you just have to work your way across the tabs on top starting with the General tab. Give you script a name. I have it to run only when user is logged in. Configure for whatever version of Windows is appropriate.
On the Triggers tab, set the schedule days and time to launch your batch file (that will launch your VBS file, which will run your Excel VBA script). Here is where I set up my 8pm daily schedule for this example. Don't forget to check the "Enabled" box.
On the Actions tab, you're going to "Start a Program". Give the name of your batch file and start it in the folder it is saved in. That's why the batch file has the full path of the VBS file.
On the Conditions tab, check the power settings that are defaulted. I've opted to turn them all off.
Lastly, on the Settings tab, review some of the runtime aspects of this job (like a runaway program that doesn't stop). These were my choices.
To test your schedule, you can again fire the batch file manually to make sure all of the moving parts are working correctly. Then set the schedule to some early time like 2pm while you're still sitting in front of the machine. If everything works, Then push the time out to when you really want it to run, and hopefully when you review it the next day it ran (if you set overnight). Good luck!!