By Zack Barresse
Today at PASS BA 2015 I spoke in a session about the topic of Excel Add-Ins. Nearing the end of the session an attendee asked an interesting question which I thought worthy of a blog post. What question should be so good/complex/interesting enough to warrant its own blog post? It’s a fairly simple question but one I get asked on a fairly regular basis.
How do I run a macro in Excel on a schedule, even if Excel is closed?
The answer to this is two-fold. First you must have some procedure in Excel you want to run. It should be scoped as public, or rather not scoped as private. The routine name should also be unique. The second part of the answer lies in creating a file which can be utilized by Windows Task Scheduler. For this I use a VBScript file (.vbs). There are two potential problems which could prohibit a solution like this. Your IT department may not allow you to either create a scheduled task or create/use VBS files. This solution assumes you can create a task in Windows Task Scheduler, which generally requires administrative rights.
Answer Part 1
For this example I have created a routine which logs a date/time stamp in column A and the currently logged in user in column B. It sets the headers and format if being run for the first time. The code must be placed in a standard module in the workbook.
' Declare variables '
Dim TargetSheet As Worksheet
Dim TargetCell As Range
' Set variables '
Set TargetSheet = ThisWorkbook.Worksheets(1)
Set TargetCell = TargetSheet.Cells(TargetSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
' Write header if not found '
If TargetSheet.Range("A1").Value = "" Then
TargetSheet.Range("A1").Value = "Date/Time"
TargetSheet.Range("B1").Value = "Username"
TargetSheet.Columns(1).NumberFormat = "dd-mmm-yyyy hh:mm:ss"
' Write date/time '
TargetCell.Value = VBA.Now
TargetCell.Offset(0, 1).Value = Environ("Username")
For this example I saved the workbook as MyBook.xlsb. I use the XLSB file format for various reasons which I will describe in a separate blog post. The above code uses the first sheet in the workbook (the sheet name isn’t specified, but can be if desired.)
Important: Once you have the code above in a file, save the file wherever you want. You’ll need to know the file path and name of the workbook file for the next part.
Answer Part 2
Now we need to set a VBScript file which will run the above routine. There are a few checks we need to do with the code in this file though. The checks we will run are:
- Make sure the file exists where we specify
- Make sure Excel is running *
- Make sure the specified file is open *
* These items are checked if they exist. If they do we will use them, otherwise create them. If we create the items we’ll close/destroy them when done. The idea behind this is to leave as little of a footprint as possible, so the environment will be left exactly how we found it.
We’re using some blanket error-handling in the file, so there is no need to check if the routine we’re calling actually exists.
The code below is saved to a standard text file (I used Notepad, but any text editor will do) which I named MyScript.vbs.
Important: There are three variables which you’ll need to set near the top of the code:
- TargetPath – the path to the Excel file
- TargetName – The name of the Excel file
- SaveFile – whether you want to save the Excel file or not
' Declare variables //
' SET VARIABLES HERE '
TargetPath = "C:\Users\Zack\Desktop\"
TargetName = "MyBook.xlsb"
SaveFile = True
' Make sure file exists before we start //
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(TargetPath & TargetName) Then
' Check for application existence, create if not open '
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.DisplayAlerts = False
Set TargetBook = ExcelApp.Workbooks(TargetName)
TargetIsOpen = True
If TargetBook Is Nothing Then
Set TargetBook = ExcelApp.Workbooks.Open(TargetPath & TargetName)
TargetIsOpen = False
' Run routine(s)
' Quit & clean-up
If TargetIsOpen = False Then TargetBook.Close SaveFile
ExcelApp.DisplayAlerts = True
Set TargetBook = Nothing
Set ExcelApp = Nothing
Set FSO = Nothing
Once you have the VBS file saved you’ll need to set it as a scheduled task in Windows Task Scheduler. (How to schedule a task in Windows 7.) For something like this a Basic Task is sufficient. You’ll need to name the task (for example ‘RunMyTestScript’), set the trigger (e.g. daily, weekly, etc.), and set the action. I recommend a time when Excel is generally not running.
Important: When setting the action, specify the full path and name of the VBS file.
When you have finished setting up the task it will show in the active task list. You’ll most likely have several other tasks listed so it’s important to name it something you’ll recognize.
That’s it, you’re done! At this point you should have two files, the Excel file and your VBS file, which should both have been saved in their final location, as well as a new scheduled task set to run the VBS file.