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
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
'Save Excel File (if applicable)
'Reset Display Alerts Before Closing
ExcelApp.DisplayAlerts = True
'Close Excel File
'End instance of Excel
'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.
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.
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.
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.
If you are making modifications to the Excel file itself, you may want to include a Save command within this script.
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!
Navigate to where the text file is located using Windows Explorer
Edit the name of the file by using keyboard shortcut F2
Change the “.txt” to “.vbs” and hit your Enter key
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:
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!
Founder of TheSpreadsheetGuru.com