You don’t need VBA to create Excel reports in 2022. Use Microsoft PowerBI Report Builder instead.
Background: Recently, my wife, who is an accountant, needed to generate a customer balance report in Excel, with multiple tables grouped by customer code, in a pre-defined format. This is not possible to do in Excel, so someone wrote a VBA script some years ago to generate it. When my wife took over the job, the script was buggy and would miss some transactions. So as a software engineer, I helped her find the most maintainable solution to do the task. Instead of fixing the old VBA code, I suggested her trying Microsoft’s Power BI Report Builder (previously known as SSRS Report Builder)
The Excel report looks something like this:
Each customer balance is grouped in a table, with customer code and name on top, an Opening balance row, and a Subtotal row. It looks simple enough, with < 10k rows in total for all customers. The only problem is you need to generate this report from below exported data.
For this use case, 10 years ago and even nowadays, writing a VBA script to generate the output Excel would make sense (for people who can write VBA). And that’s exactly what happened. Someone prior to my wife in the job wrote a VBA script several years ago to generate the Excel report line by line. And it worked well until this year when the data exceeds 1000 rows. That’s when records would go missing, lots of them at a time.
My wife, who has accounting background and no experience in writing code, needed to manually checking and fixing those missing records. When I found out about this, my first thought is to fix the VBA code. Then I think, even if I fix this, there are dozens other reports that might have buggy code as well, so it’s not maintainable to do it that way. I want to help my wife, but I need to find a solution that my wife can maintain it herself.
So I immediately think of Microsoft Report Builder (which is mostly used for enterprise applications, pulling data source from SQL Server). These report builders are probably some of the first low-code applications for a normal user, before low-code and BI tools were popular. Another alternative is Jasper Report Studio CE(free and open-sourced), or SAP Crystal Report (needs to purchase a license), but these are section-based reports and does not work well with Excel output. When I do a bit more research, it seems Microsoft Report Builder is free to download, but might have some licensing tied to SSRS/SQL Server, and need Administrator permission to install. So I ended up with Microsoft Power BI Report Builder, which is free licensed and available on Microsoft Store, requiring no Administrator permission to install (this is not Power BI desktop as most people in BI/Analytics would know, but Power BI paginated report builder, which looks almost identical to Microsoft Report Builder)
I only need around 10 minutes to create a RDL template that generate the exact Excel reports. Here’s the RDL template sample.
It consists of just one Tablix, with a row group by CustomerCode.
The important things to note here are:
- You need to choose “Enter Data” for Data Source connection type, and enter data by copy-paste from Excel to Data Set Query Designer view.
- Before copying from Excel, you need to clear formatting for Numeric data and use correct Date Format locales for DateTime data
- You need to create new columns in Data Set Query Designer manually the first time. By default, column data type is string (text). You need to right click in the column to change data type to Date, Integer, or Float (numbers with decimal parts) accordingly
- The UI and most Functions looks very similar to Excel, with Merge and Split in Table very easy, and Sum, IF functions in Expressions editor. Expression is similar to Excel Functions.
When this is done, click Run to preview the output. Click Design to modify the template again.
When the preview looks correct, click Export > Excel to export the Excel output.
I hope this would be an inspiration to try Power BI Report Builder to automate Excel reports, that would otherwise impossible to do in Excel without VBA. And an inspiration to help people with no programming background to do their daily tasks better with the help of low-code software like this, instead of learning VBA in 2022.
