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:

1. Executes the Module.MyMacro VBA Macro in Workbook Book1.xlsm which is situated in the same folder
2. Sleeps for 5 seconds
3. Repeat

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!