Excel Automation Tools

Excel automation generally involves building code to interact with Excel and automatically perform tasks.  This article contains a comprehensive list of the best coding tools and software for Excel automation.  Some tools (ex. Power Query) can be used to automate Excel without any coding knowledge. Others tools help professional developers create complex Excel add-ins.

If you’re not an experienced programmer we recommend the first three tools as your first steps into Excel Automation:

• VBA Macro Recorder – Record actions in Excel, and replay recorded Macros to repeat processes.
• AutoMacro – Reduces VBA’s learning curve for beginners. Makes coding VBA more accessible to non-programmers.
• Power Query – No coding knowledge required. Easily manipulate data in Excel and save queries for future use.

In the rest of the article you’ll find Excel automation tools for Python, SQL, Java, C#, C++, and D.

VBA Tools

You may already know that Excel has the ability to record Macros. Macros allow users to record their actions in Excel, so that they can be easily repeated in the future.

Macros are saved as VBA code procedures. VBA is Excel’s built-in programming language (short for Visual Basic for Applications).  VBA is stored in the Visual Basic Editor, which is built directly into Excel and other Microsoft Office software.

For an experienced Excel user, VBA is relatively easy to learn because of it’s integration with Excel. It’s also usually the best language to use when automating Excel. You can learn VBA with our completely free, interactive VBA Tutorial.

VBA – Macro Recorder (free)

The Macro Recorder records your actions as VBA code. Macros can be replayed to repeat your recorded task(s).

See the ‘Record a Macro’ section of our VBA Tutorial for instructions for recording Macros. Note: First, you may need to add the Developer Ribbon, you will find directions to do so on the VBA Tutorial page.

The Macro Recorder is a fantastic first step to automating Excel. However, you will quickly see that Macros have limitations.  It can often by difficult or impossible to record Macros that work exactly the way you would like.  The code often requires manual updates in order to function properly and some automation isn’t possible with the Macro Recorder.

This is where AutoMacro comes in….

VBA – AutoMacro

AutoMacro is an add-in that installs directly into the Visual Basic Editor.

AutoMacro contains four toolbars:

• Code Library – Easy access to 230+ commonly used code fragments. Learn More
• Code Generators – Generate procedures or other code blocks from scratch. Beginners can program complex procedures with very limited knowledge of VBA. Learn More
• Custom Code Library – Create and save your own code fragments for easy access and share with team members. Learn More
• Tools – A suite of coding tools for experienced developers. Learn More

AutoMacro was developed (by us at AutomateExcel) to allow anyone to code VBA with very limited coding knowledge, while also teaching basic concepts. However, it also has many powerful tools and time-saving features for more advanced programmers.

The Code Library makes it easy for anyone to create VBA code from scratch:

The Object Code Library in particular makes it easy for anyone to interact with Excel objects, while learning about the object structure:

We recommend that anyone interested in Excel automation should learn at least some VBA. Using AutoMacro will help decrease the learning curve.

Data Tools

Power Query / M (free)

Power Query is a free tool from Microsoft. Download Power Query

It’s used to extract, transform, and load data in Excel. Each step is stored in M (the language behind Power Query), so that your processes can easily be automated.

If you work with data, Power Query might be the most useful Excel tool to learn. Luckily it’s also relatively easy to learn Power Query compared to VBA or even Excel Formulas.

SQL – QueryStorm

SQL is a querying language used to interact with tables and databases.

QueryStorm is an Excel software package that allows you to make SQL queries on data stored in Excel. It also allows you to fetch data using .NET libraries and improves database connectivity.

It’s one of the coolest and best-designed Excel add-ins that we’ve seen.  If you know SQL (or want to learn) and work with large data sets in Excel, you should try it.

Excel Developer Tools

Python – Multiple Tools

Python is a general purpose programming language with excellent support for scientific computation. It is popular among data scientists and web developers, and has found applications in finance, engineering, research and many other industries.

There are several recommended tools for using Python and Excel:

PyXLL embeds Python into Excel, enabling users to create fully featured Excel Add-Ins written in Python.

xlwings is a popular free and open-source library to program Excel with Python.

There are also other packages for interacting with Excel from Python: OpenPyXL and Pandas to name two.

Which Python tool to use?

The important question to ask is: Are you trying to use Python within Excel as a replacement to VBA or do you want to interact with Excel from within Python?

If you want to interact with Excel from Python, then the basic free Python Packages are probably fine. If you want to use Python to replace VBA within Excel then PyXLL is your best bet. xlwings is also an option if you want to use a free open-source solution.

PyXLL does a good job breaking down the various tools for Automating Excel with Python in this article.

Java – Jinx

Java is an object orientated class based programming language with excellent developer tools and a vast array of third party libraries. Its reported that over 3 billion devices currently run Java, with applications ranging from mobile phone apps to high frequency trading platforms and everything in between.

Jinx is an Excel Add-In that bridges Excel with Java and other JVM languages such as Scala and Kotlin, allowing you to create UDFs, macros, menus and more using Java.  Jinx was created by the development team behind pyxll.

The Jinx/PyXLL development team also created a Java library for calling into Excel via COM using an open source package: com4j. The library can be found on GitHub.

C# / .NET – Excel-DNA (free)

.NET is a framework for programming Windows-based applications (and more). C# is a programming language that can be used with .NET to develop applications. Using C# and .NET you can create add-ins for Excel.  Add-ins created with .NET can be much faster, stable, and complex than add-ins created with VBA.

From Excel-DNA’s website: Excel-DNA is an independent project to integrate .NET into Excel. With Excel-DNA you can make native (.xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more. Your entire add-in can be packed into a single .xll file requiring no installation or registration.

One of the primary benefits of using Excel-DNA is that your add-in can be contained in a single .xll file. No installation is required, which is very useful when deploying solutions to in corporate environments that prohibit installations.

C++ – XLL Plus

C++ is a general purpose programming language.

XLL Plus is a toolkit to aid C/C++ programmers in the construction of Excel add-in libraries. According to their website it’s used by many of the top investment banks. It’s extremely expensive ($1,345 at the time of writing), so it’s not for casual users.

D – excel-d (free)

excel-D can be used to build Excel add-ins in the D language

Excel Automation FAQs

How to Automate Excel Spreadsheets?

You can automate Excel spreadsheets by writing Macros with the VBA programming language. Technically, you can record Macros without writing any code, but advanced automation will always require some code writing / editing.

What is Excel Automation?

Excel automation is programming Macros to automation tasks in Excel. Macros are typically written in the VBA programming language (included in Excel), but they can also be written in Python or other languages.

How to Automate Excel reports using Python or Java?

Excel reports are typically automated using the built-in programming language VBA. However, they can also be automated with Python, Java, or other languages. We recommend using PyXLL or Jinx to help with Python and Java automation in Excel.