How do I run a macro at a specific time in Excel?

April 29, 2019/ Chris Newman

I’m currently working on a consulting project where a VBA macro within an Excel file needs to run every morning before folks get into the office. As I work through getting this setup, I figured I would document my solution for everyone.

This article will go through how to utilize the Windows Task Scheduler application (don’t worry, this comes installed with most Windows PCs) to automatically open an Excel file, run it’s VBA code, save the file, and finally close the Excel file. With this solution, you will be able to customize the frequency and exact time(s) this automation will need to be executed. The ONLY CATCH to this solution, is that the PC will need to be on during the scheduled time.

Sorry Mac folks, this is only a PC solution. However, if you know your way around a Mac, you may be able to figure out how to implement a similar solution on the Mac OS.

What You Will Need

To fulfill this solution, we will be utilizing a few programs outside of Excel. You should already have all of these programs pre-installed on your PC.

• Windows Task Scheduler

• Notepad

• CScript (Located: C:\Windows\System32\cscript.exe)

To launch Notepad and Windows Task Scheduler, simply type in their respective names into the search bar on your Taskbar and open the applications.

Creating the VBS File

We will be utilizing a Visual Basic Script file (.vbs) to list out a set of automation instructions. If you are familiar with VBA, this is essentially the same thing with the “Sub()” and “End Sub” statements and it will need to be written inside of Notepad.

Launch the Notepad application and paste the following code on the left into the text editor (the colors won’t display in Notepad).

'Input Excel File's Full Path
  ExcelFilePath = "C:\Users\chris\Documents\My File.xlsm"

'Input Module/Macro name within the Excel File
  MacroPath = "Module1.MyMacroName"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = True  'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
  ExcelApp.DisplayAlerts = False

'Open Excel File
  Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
  ExcelApp.Run MacroPath

'Save Excel File (if applicable)
  wb.Save

'Reset Display Alerts Before Closing
  ExcelApp.DisplayAlerts = True

'Close Excel File
  wb.Close

'End instance of Excel
  ExcelApp.Quit

'Leaves an onscreen message!
  MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation

After you have pasted in the code, you will need to modify it to suite your needs.

1. You will need to change the value of ExcelFilePath variable to point to the exact file path of the Excel file you would like to open. Make sure to include your Excel’s file name and extension at the end of the path.

2. You will need to change the value of MacroPath variable to direct the automation to the precise macro you would like to run. Make sure you reference the Module name first and then the macro’s name.

3. You can decide whether or not you would like the Excel application to visible show on the computer screen while all this automation is happening. Typically, I will use the True value while testing (just for a sanity check) and when everything is working smoothly, switch it to False.

4. If you are making modifications to the Excel file itself, you may want to include a Save command within this script.

5. Finally, you can choose whether or not you would like a message box to show, allowing the user to be notified if the automation ran successfully. Again this might be useful for testing or building up trust with your users that the job is actually running.

After you have made all your modifications to your Visual Basic script, Save the text as a .txt file via Notepad. For this example, I called the .txt file “Macro Launcher”.

Converting .txt to .vbs

Now we will finish up by converting our newly created text file into a Visual Basic Script file (or .vbs file). This is extremely easy to do!

1. Navigate to where the text file is located using Windows Explorer

2. Edit the name of the file by using keyboard shortcut F2

3. Change the “.txt” to “.vbs” and hit your Enter key

4. You will be prompted to confirm the changing of the file type. Click Yes

After following those steps, you should see your brand new .vbs file within Windows Explorer

Creating Your Windows Automation Rule

Finally, we will create an automation rule (or task) using a native Windows application called Windows Task Scheduler. To find this hidden gem, just type “Windows Task Scheduler” in the search bar of your Taskbar and launch it.

You should see a Task Scheduler dialog appear that looks something similar to the image below. If you do not see the Actions pane to the right, click the last icon in the application’s toolbar.

To create a new task, simply click Create Task.

I will walk through each tab of the Create Task wizard, so you can get a good idea of all the settings you will most likely want to modify.

Create Task - General Tab

In the General Tab, you will be able to provide a name and a description (optional) for your automation task. Make sure to make your name descriptive so it will be easy to find in the long list of tasks that other applications on your PC have already created.

You will also be able to determine you would like the task to run if you are logged in/out of your PC. I assume most of you will want to enable the task to run while your computer is logged off, especially if your task is running on off hours.

Create Task - Triggers Tab

In the Triggers Tab, you will be able to create in-depth scheduling rules for your automation. In the below example, I am creating a Trigger to execute the Task every day at 6:30am.

There are also options in the Advanced Settings section to repeat the task after the initial trigger. For example, you may what to refresh a stock portfolio model every 10 minutes after the stock market opens. The Advanced Settings section gives you the freedom to repeat the task every X number of minutes after the initial trigger has executed.

You do have the freedom to create multiple trigger rules if necessary.

Create Task - Actions Tab

The Actions Tab is where you will map out which program you would like to launch. In our example, we are going to want to run the .vbs file we created that will open and run our Excel macro. We will utilize a program called CScript to execute our .vbs file. You should have this program already installed if you have a Windows OS. Below is the path to the application for your reference:

• Program/Script: "C:\Windows\System32\cscript.exe"

Next, we will want to reference the file to open via CScript. Paste in the full file path of the .vbs file we created earlier. Mine looks something like this:

• Add Arguments: "C:\Users\chris\Documents\Macro Launcher.vbs"

PLEASE NOTE: Both of these inputs MUST be surrounded by double quotation marks or else your task will not function!

Create Task - Conditions Tab

With the options in the Conditions Tab, you can further customize the determining factors for when your task should run. The only one of importance to me is the option highlighted below, which will allow your task to run even if your PC goes to sleep.

Create Task - Settings Tab

Finally, the Settings tab provides a few additional options to customize your task. I have highlighted one important option below if you are incorporating a “completion notification” message box into your Visual Basic script (FYI we are using a message box in the the example).

It is important to understand that if a message box appears on the screen, by default your task cannot repeat itself until the message box is closed by the user. To get around this, you can tell the task to kill itself if and rerun if no one ended up closing the message box during the time between triggers.

Modifying Your Task In The Future

If you ever need to modify or delete you task in the future, you will need to select it from the list of tasks create on your PC (see #1). After your select your desired task, you should see a plethora of options available to you with the Actions Pane. To modify your task, simple select the Properties option and you will be taken back through the Create Task wizard.

You Now Have An Automation!

There you have it. With the utilization of a couple programs, you now have a Windows task setup to execute your macro(s) automatically. This will save you time and hassle going forward…and needless to say make you look extremely smart to all your co-works! I hope this article helped you get the solution you were looking for.

Let me know what you were able to automate in the comments section below!

About The Author

Hey there! I’m Chris and I run TheSpreadsheetGuru website in my spare time. By day, I’m actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the “real world” and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company.

Through my years in the corporate world, I’ve been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins, from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If you’d like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I’ve learned over the years, you can sign up for my free newsletters. I hope I was able to provide you with some value today and I hope to see you back here soon!

- Chris
Founder, TheSpreadsheetGuru.com