Run VBA code from Excel workbooks automatically using Windows Task Scheduler

Suppose you want to run some VBA code at specific intervals, maybe every day or every week. Using Windows Task Scheduler we can set up our code to run whenever we want.

Step 1: Create Excel workbook

This workbook will contain the code we want to run. For this exercise, I use the Workbook_Open Event so that the code runs automatically when the workbook is opened. I put the following code into the ThisWorkbook module and save the workbook somewhere:

Private Sub Workbook_Open()
  MsgBox "workbook is open, put some code here!"
End Sub

This is just for proof of concept. Your code will be more complicated, right?

Step 2: Create .vbs file

The VBScript file is what we will tell Windows Task Scheduler to run. This file will contain the instructions necessary to start Excel, open a workbook and run the code within. The difference between VBA and VBScript is minimal:

• No built-in constants

Instead of the constant xlCalculationAutomatic, we must use it's value instead:
-4105

. Use the Object Browser (press F2 when in the VBA Editor) to locate the values for a given constant, or visit this article to download a list of enumerations for import into your VBA project.

• All variables are Variant and Late Bound

All you do is write Dim x and Dim y for any variables you need. You do not include the "As …" part of the declaration. Everything is a variant and you can assign any value to any variable. Reminds me of PHP.

This is the script that will open our saved workbook, triggering the Workbook_Open Event to execute our code.

Dim xl, xlwkbk
set xl = createobject("Excel.Application")
set xlwkbk = xl.Workbooks.Open("C:\My_Files\Book1.xls")
xlwkbk.Close false
xl.Quit
set xl = nothing

I paste this into Notepad and save it as myfile.vbs. You can do many other things with the VBScript file alone, depending on your needs:

• Use FSO

Ex: You want to delete or copy files between folders on a preset schedule.

• Use WMI

Ex: You want to count the number of read-only files in a folder, or check the amount of free disk space, at a predetermined time.

• Use MSXML

Ex: You want to call a web API and cache the response every weekday.

These things are possible without even opening Excel or using VBA.

Step 3: Create .bat file (optional)

This step is optional, but I found it useful. Instead of telling Windows Task Scheduler to run a .vbs file, you can ask it to run a .bat file instead. In this exercise, my .bat file is merely a front end for the .vbs file, but you can perform tasks entirely in the .bat file (if you know how to write the code — I don't remember how anymore!).

So I create a text file with extension ".bat" and put this into it:

@echo off 
start %systemroot%\system32\cscript.exe "C:\My_Files\myfile.vbs"

cscript.exe is the program that will run any .vbs file and it is located in the Windows\system32 folder (read more about CScript.exe here). We simply pass the name of the .vbs file to it, and it runs the code for us.

%systemroot% is an environment variable that returns the path where Windows is installed. Type ?environ("systemroot") into the Immediate Window in the VBA editor if you want to see its value for your system. (I could not locate any environment variable that directly returns the location of the system32 folder.) We simply append the system32 path to that to get the path where cscript.exe is located.

Step 4: Set up Windows Task Scheduler

In Control Panel, click "Scheduled Tasks" and click "Add Scheduled Task". Click "Next" and then choose a program. It doesn't matter because we are going to change it later.

Click "Next" and enter a name for the task. Choose a frequency and click "Next".

Choose a start time and date and click "Next".

You may be prompted for username and password. Enter them and click "Next".

The final dialog shows you the options you've chosen. Click "Finish" but make sure you check the "Open advanced properties" checkbox first.

Select .bat or .vbs file

Now the properties dialog for the task will open. Clear out the "Run" and "Start In" textboxes. Now we have four possible options for the "Run" textbox:

1. Run BAT file using fully qualified reference
2. Run BAT file assuming Windows file association
3. Run VBS file using fully qualified reference
4. Run VBS file assuming Windows file association

If you followed Step 3 to create a .bat file then you would use option 1 or 2. If you only want to run a .vbs file, you would use either option 3 or 4.

If you are able to double click either the .bat or .vbs file and run it, then your computer already knows what program to execute for .bat and .vbs files. You would then, depending on whether you want to run the .bat file or the .vbs file, use just the filepath for either file:

Option 2:

Option 4:

If you feel like showing off, or your computer does not have an associated program for .bat or .vbs files, you need to qualify the filepath with the name of the executing program:

Option 1:

For .bat files, instead of

"C:\My_Files\myfile.bat"

use

%systemroot%\system32\cmd.exe "C:\My_Files\myfile.bat"

Option 3:

For .vbs files, instead of

"C:\My_Files\myfile.vbs"

use

%systemroot%\system32\cscript.exe "C:\My_Files\myfile.vbs"

This tells the task scheduler to run the given program, passing the filepath as an argument, which will open our workbook and execute our code.

Benefits

For recurring operations, the benefits of this approach are tremendous. We are no longer required to keep Excel open to run a macro. The task scheduler dialog does all the heavy lifting, so we don't have to write any VBA that figures out when to run the code. We also don't need a workaround like asking Outlook to stay open and run a recurring task.

Download sample files