Reply to post:

'Microsoft Office has been the bane of my life, while simultaneously keeping me employed'

thames

I was also thinking that I would use use Python with an off the shelf library to suck the data out of the master file, figure out the report, and then generate the report files. That's assuming that MS Excel is even needed anywhere in the process at all and you can't simply go from database to final presentation format. I wouldn't be surprised if doing the report in Python (or some other similar language) cut the run time down to being limited only by how fast the PC could write the output files to disk (i.e., seconds).

Many years ago I re-wrote a report system for a critical product quality system from a database + proprietary language (which will remain nameless to protect the guilty) to using a "proper" programming language with in-memory data. I had inherited this system and had previously put much optimisation work into cutting the daily report run time from "all night long" to "a few hours".

Upon testing the all new "proper" system with real data, the report was on the screen before I finished raising my finger from the "enter" key. I spent much time with a debugger before convincing myself that it was working correctly and it really was that fast. That made a big impression on me which has lasted to this day.

The thing which makes me most nervous about VBA + MS Excel reports though is just how hard it is to understand what's in there and what it's doing. Nobody but the original author seems to understand it. Code auditing, unit tests, revision history (who changed what and why), etc. are usually non-existent. And yet you can find many companies whose bean counters quibble for hours over the provenance of every expense receipt, but are perfectly happy to plug those numbers into a rat's nest of VBA macros and an untraceable chain of spreadsheets to come up with the company's bottom line. It's a horror show that I'm happy to stay well away from.

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon