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).
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 tasks – Create 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#