Tales from the Pivot Table

I’m working on a little project for some of our partners. Basically pulling some data out of Fastpath and having Excel pull it into a pivot table via a macro. In the past, I’ve tried to stay away from macros and VBA when I could. Frankly, the security issues macros can cause sometimes force people to shy away from using them. This time I had no choice.

I started with code to import a report and pull out the header to make pivot table creation easier. Then came code to create a pivot table. I got everything working for one ERP system, but I need this work on multiple ERP’s. My first thought, the sloppy thought, was to just replicate the code and make multiple Excel files. That thought didn’t last long. Too hard to maintain.

After that I decided to copy parts of the code and give users the chance to select their ERP and drive the code selection that way. That’s when things got interesting. In testing I realized that my Excel code simply skips pivot table columns if they aren’t present. For example, if D365 uses “Role” and Oracle EBS uses “Responsibility”, I could put both of those items in the code, but if I’m running this for D365, Responsibility won’t come in because it’s not in the file. Excel just ignores that Responsibility is missing and moves on. This made coding for multiple ERP’s much easier. I no longer needed to make users pick an ERP system. With the right code, it just works for each ERP I’ve setup.

It wasn’t this easy of course. I found a bug in my v1 that blew out an entire worksheet as I moved to v2. Oops. Pulling out the header proved to be more challenging than expected since the header can be different lengths. I still have some testing to do, but all the preliminary stuff looks good.

So what’s the point? Well 1) It’s cool that Excel VBA doesn’t error out when building a pivot table if a field is missing, it just moves on. 2) Projects like this take some deep work. They need large blocks of time and they can’t be rushed. Distractions kill work like this. Twitter, email, slack, all of it. It’s a lot of “no’s” to tackle a project of even limited complexity.

Oh, and for those of you asking, yes, I built the whole thing in Excel for Mac and it runs just fine in Excel for Windows.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.