Some time ago I posted an article on how to run an Excel VBA Macro using VBscript and cmd to save time or to be able to run multiple Macros across a number of Excel files without actually needing to open them. Well, I decided to extend this example to allow periodic execution of Excel Macros e.g. in case your macros have to carry out some periodic tasks like load data into a database etc.
How to schedule VBA Macros to run automatically?
The solution is quite similar to the one posted on my previous article with one minor modification…
What we need to do is execute our VBA Macro (can be Excel, Access etc.) from a VBScript script file. To make sure it runs automatically we need to schedule it to run from startup by placing it in the Windows Startup folder. Follow the steps below to setup the script.
Create VBScript to run Excel periodically
First create an empty *.vbs file and input the following code snippet:
RunMacro Sub RunMacro() dim xl,path,xlBook path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".") Set xl = CreateObject("Excel.application") Set xlBook = xl.Workbooks.Open(path & "\Book1.xlsm", 0, True) xl.Application.Visible = False xl.DisplayAlerts = False xl.Application.run "Book1.xlsm!Module.MyMacro" xl.ActiveWindow.close Set xlBook = Nothing xl.Quit Set xl = Nothing WScript.Sleep(5000)'New line: Sleep for 5 seconds RunMacro'New line: Run the Macro again End Sub
Replace the highlighted rows with your Workbook name and VBA Macro name.
What does the VBScript do?
So basically what it does is:
- Executes the Module.MyMacro VBA Macro in Workbook Book1.xlsm which is situated in the same folder
- Sleeps for 5 seconds
Add the VBScript to Startup
To make sure the Macro will run periodically and not require any manual touch, you may want to add it to your Startup folder. This will ensure that the script will start running as soon as you turn on your workstation and will stop as soon as you close.
Add to Task Scheduler
Many times you will want to configure very specific scheduling of Macro execution. For this purpose I suggest using Microsoft Windows built in Task Scheduler.
Turn Off the periodic VBA macro
What if you want to turn the script at any moment in time? You might as well turn on the Windows Task Manager. There is an easier way. Just create the following *.bat file and run it when you want to close the periodic script:
taskkill /F /IM wscript.exe
Now you can create Excel Macros and use them to execute certain tasks at intervals automatically!