By Zack Barresse
Synopsis
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.
The question
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub MyRoutine() ' 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" End If ' Write date/time ' TargetCell.Value = VBA.Now TargetCell.Offset(0, 1).Value = Environ("Username") TargetCell.Resize(1, 2).EntireColumn.AutoFit End Sub |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
' Declare variables // Dim ExcelApp Dim FSO Dim TargetBook Dim TargetSheet Dim blnOpened Dim TargetIsOpen Dim TargetPath Dim TargetName Dim SaveFile ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 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 End If ' Run routine(s) ExcelApp.Run "MyRoutine" ' ' ' ' Quit & clean-up If TargetIsOpen = False Then TargetBook.Close SaveFile ExcelApp.DisplayAlerts = True ExcelApp.Quit Set TargetBook = Nothing Set ExcelApp = Nothing End If 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.
From your VBS procedure, you could skip
Set ExcelApp = GetObject(, "Excel.Application")and simply use
Set ExcelApp = CreateObject("Excel.Application")and a new instance of Excel will be used to run the Excel VBA procedure. You don’t need to use ExcelIsOpen to keep track of the initial state of the instance, since it was a new instance and you’ll destroy it when you’re done.
You may get an error if the file is open in another instance and you try to set it, but as far as I’m concerned it’s best practice not to store data in the same file that has code.
Very true Jon. The idea was to leave as small an footprint as possible, so use the app if we found it running. Sometimes I have a tendency to leave the computer on, as well as Excel, so this method used less memory. But good point nonetheless. 🙂
Unless there’s some kind of memory leak, opening and closing a separate instance of Excel shouldn’t matter. Sure, the user may notice a slight drag on his system, but the new instance need never be made visible. This way, it should be less disruptive than if a file suddenly opened up, ran some code, and then closed in the user’s instance, where he might be doing some data entry or other editing.
Good point indeed! I’ll make the adjusted to the code showing the difference.
By putting the VBA routine into the VBS routine it is also possible to manipulate XLSX-files.
Yes, you could very well do most of the heavy lifting within the VBS file. The primary reason I generally don’t is the limited object model we have for VBScript. The Excel OM is much more robust.
I thought VBS could access all of Excel’s OM.
No, not all unfortunately. For example (off the top of my head) I don’t think it has the Format() function.
Hmmm, yeah, it’s complicated. You can’t get to Format, because it’s in the VBA library (VBEx.DLL). An Excel VB Project automatically has a reference to this library.
If you create an Excel object in your VBS, you do have access to anything that falls under Excel’s Aplication object. So while you can’t use VBA.Format, you can use xl.WorksheetFunction.Text.
Dim xlSet xl = WScript.CreateObject("Excel.Application")
xl.Visible = True
MsgBox xl.WorksheetFunction.Text(123, "0.000") ' works
MsgBox vba.strings.Format(123, "0.000") ' fails
xl.Quit
Set xl = Nothing
I’m not sure whether you can access any libraries like VBE.DLL from VBS.
This throws an error in VBS, claims it can’t find the file:
set vba = wscript.getobject("C:Program FilesCommon FilesMicrosoft SharedVBAVBA7.1VBE7.DLL")Probably the DLL does not provide an interface that VBS can recognize.
Great resource Zack! Now that I can run a macro while I’m sleeping, is there anyway to get Excel to turn on the coffee pot in the morning? 🙂 Thanks!
Not yet, but I love the forward thinking! 🙂