Just a quick update here as I came across something that should be pretty interesting for anyone who does any VBA programming in Excel.
I’m currently working for a client designing and running reports out of their main data system, I do almost all of this work in Python. The output of the Python process is a series of Excel files with data specific to one customer in each file. These files then need to be formatted into a pivot table so that the team receiving the files can easily do analysis and present the data to the customer. Those who are familiar with Python, should know that its ability to format Excel files is not all that great.
So, after a few iterations of opening each file and manually formatting and creating pivot tables, I finally decided to write a VBA subroutine to do this for me. I was careful to keep the language scalable and usable for every other Excel file. After the subroutine was written, I then exported the module and saved it to the project’s GitLab repository. Now, whenever I need to run this report, I can import the module and run the subroutine. What used to take 10-15 minutes per file, now takes a few seconds per file, actually not that great when I’m billing hourly.
Nonetheless, this will work for now, at least until I get around to building a dashboard in Power BI or Splunk, to get this process out of Excel entirely.