Tag Archives: cmd

vba with vbscript

Schedule VBA Macros to run periodically via VBscript

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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

Schedule VBA: Add VBScript to Startup folder
Add VBScript to Startup folder
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.

Turn Off the periodic VBA macro

Schedule VBA: Turn off VBA MacroWhat 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!

invoke vba

Running Excel VBA from VBscript

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Today’s post is going to be very niche. But as I am often bored lately and playing around as a Data scientist in the Kaggle contests I occasionally run into these strange places where I need to make some peace of ultra-efficient code and needing to automate as many things possible.

Run VBA from VBscript

So I ran into the issue of having to run some Excel macros automatically from the Windows command line (cmd). I had some Excel file which was much easier to process in VBA than in R (this reminds me to have to learn Python soon). But then again I develop my machine learning models in R and not Excel. So I needed a way to execute Excel macros from R… to do some of the dirty work. How to do that? Fortunately R has the Shell command which invokes the Windows shell (cmd).

VBscripts are executable text files with the vbs file extension. They are supported natively by Windows and can be executed without MS Office. VBscript is almost identical to VBA hence most VBA scripts can be exported to simple VBscript files.

Want to learn how to make an App without Excel using VBscript? Read this post on HTA

The only thing left was to write a VBscript which would allow you to run an Excel macro (run VBA from VBscript). Here is an example:

'This runs the macro below
RunMacro

'The RunMacro procedure
Sub RunMacro() 
  Dim xl
  Dim xlBook      
  Dim sCurPath
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Workbook.xlsm", 0, True)      
  xl.Application.Visible = False
  xl.DisplayAlerts = False     
  xl.Application.run "Workbook.xlsm!Module.RunMacro"
  xl.ActiveWindow.close
  xl.Quit
  Set xlBook = Nothing
  Set xl = Nothing
End Sub 

Cool right? I was thinking what use cases can be out there for using this code. These are a few I came up with:

  • Scheduled tasksCreate VBscript to run on system start-up or other similar events. This can be useful if e.g. you are an analyst which needs to refresh data on a daily basis. Running the Excel macro tasks automatically will save you a lot of time.
  • Running Excel macros from other applications – this is basically the use case I quoted above. However, keep in mind that running shell (cmd) Windows commands if possible virtually from any platform e.g. Java, C#, ASP.NET etc.
  • Parallelism – Excel does not allow you to utilize more than 1 thread when running Excel macros in a workbook. Ok. But what if I split my workbooks and run them from VBscript? Running all these workbooks in separate processes from VBscript will indeed be an elegant approach. Much cleaner than opening quadrillion workbooks… Although please do not do this. Excel is not meant to accomplish such tasks – use R or Python or even C#