Run Scheduled Macro

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.

 

 

 

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

 

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.

 

12 thoughts on “Run Scheduled Macro

  1. 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.

    1. 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. 🙂

      1. 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.

    1. 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.

          1. 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 xl
            Set 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.

  2. 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.

Leave a Reply to Zack Cancel reply

Your email address will not be published. Required fields are marked *