Ways to Automate Excel Tasks

Anything beats VBA, right? An examination of available options

If you’ve ever had to do any automation tasks in Microsoft Office applications, and specifically Excel, then you may have experienced the pain of dealing with a language that’s not quite complete – namely VBA.

VBA has documentation that’s spread across dozens of websites in which experts disseminate their hard-won knowledge. Microsoft has some documentation online, but it tends to be terse. The art of VBA coding is one of trial and error. The most common experience among many would-be VBA programmers is to copy and paste from online examples. As soon as they have something that approximates what they want then they cease all further work. Frequently, any knowledge gleaned from this is quickly forgotten.

And yet, the businesses I support love Excel. An outgrowth of that affection is the demand for automating tasks within that application. Most of the time this is fine, and the Excel macro recorder can take care of most user automations admirably.

Sometimes, however, these automations cross the line into application territory. For example, when Excel is performing database reads and writes. Or when Excel is sending emails on behalf of users. Or when an Excel workbook is copied across hundreds of users and goes beyond simple formatting and charting tasks.

Frequently we are constrained by perceptions of complexity, or time, or budget and must use Excel to accomplish these tasks. But is VBA the best tool to use for doing this? There are ways to perform tasks in Excel using external tools or even plugins.

I’ve got a couple of things I’ve found over there years for your perusal.

Consider using PowerShell for Excel automation tasks:

https://www.red-gate.com/simple-talk/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/

http://sqlmag.com/powershell/update-excel-spreadsheets-powershell

PowerShell is appealing because it’s made to be a scripting language that binds different Windows applications together. It has great documentation, even when offline, and it gives us the powerful ability to read in an Excel spreadsheet like a table using SQL syntax to filter the data and then we may do with that data as we please. In terms of speed and maintainability, where VBA will eventually start to chug when doing some tasks – PowerShell remains speedy.

And yes, you can call PowerShell scripts from VBA for a nice marriage of the two.

runSendreports = Shell("Powershell.exe Path/To/The/Script.ps1", vbNormalFocus)

The drawbacks you’ve probably already guessed at by now. The Execution Policy needs to be set correctly on each client PC. Also, the ODBC drivers in PowerShell have a few bugs (which the author of the above article goes into, including work arounds – which is nice). From a maintenance perspective – you know have a package of workbooks and PowerShell scripts that must be maintained and deployed together. There are likely good solutions to the deployment problem and I welcome feedback on that.

What about Python?

Did you know that there are plugins that allow you to write all your scripts in Python instead of VBA? Does that sound appealing? Check these out:

https://datanitro.com/ Get the full power of the Python ecosystem in your spreadsheet! For just a small licensing cost…

There is an open source equivalent:

https://www.xlwings.org/ With a 3-clause BSD license: https://tldrlegal.com/license/bsd-3-clause-license-(revised)

All you must do is learn Python and you’re good to go. If you have a choice to make between learning Python or VBA, well… Well. Python is certainly more broadly applicable than VBA.

These two options only scratch the surface – being that they provide the Python programming language to tackle Excel tasks I call them out by name.

However, there are many more niche options that tackle a specific problem within Excel. It’s certainly worth researching so you’re not stuck debugging VBA.

The drawbacks are the cost of the software and/or whether the license will be an impediment. These two drawbacks should not be understated. Even seemingly benign licenses might be rejected by an organization’s legal department and the reason for that objection will rarely be transparent. A drawback specific to Python is if you use a Python library then you’ll need to think of a way to keep that library bundled with your spreadsheet. I’m unsure if either of the linked plugins provide a solution for that problem.

Another option is to make your VBA writing experience more pleasant

Given that there is no way to write and run VBA outside of the Visual Basic for Application Editor (VBE). (You can technically copy and paste bode between a text editor and VBE if you enjoy that sort of thing)

Free
---------------
MZTools: www.mztools.com
VTools: www.skrol29.com/us/vtools.php
Indenter: http://www.accessui.com/FreeAccessTools/SmartIndenter/tabid/74/Default.aspx

Paid
---------------
Find and Replace: www.rickworld.com
Total Visual Code Tools: http://www.fmsinc.com/MicrosoftAccess/VBACodingTools.html

Hopefully this will direct you to some resources that can help you get up and running on automating your Excel tasks without forcing you to use VBA, or if you do use VBA hopefully you can make the experience marginally more pleasant.