Two years ago I intended to learn Python. Then I never did. And there’s a story to that.
What brings this up is an article I saw online a few days ago, “VBA vs. Python: Which is the best tool for Excel automation.”
It began, as many such things do, with a work problem. We needed to do bulk renames of artwork to make it easier for the Graphics department to deal with. Artwork would come over from Penguin Random House named with either the UPC or the ISBN, which is great for computers but terrible for humans.
The ideal solution, in my mind, was this. We had the UPCs and the ISBNs in our database. The script could iterate through a folder of artwork, take the UPC or ISBN from the file name, match it to the record in the database, and then link the artwork to the record. No need for Graphics to get involved and do it manually. “Meet the artwork where’s it at,” is how I put it.
But, as a secondary solution, because you always need a secondary solution, we received a spreadsheet with the artwork that contained both the UPC or ISBN and the title of the book. So, if the automated solution wasn’t workable, a script to do a batch rename on the artwork files, giving them human-readable names, would be a workable solution.
So, I did some research. I bought a Humble Bundle of books on learning Python. I even found some Python scripts online that would do bulk renames from spreadsheets. I cobbled something together, tried it out on my computer at home, renaming a bunch of artwork (basically, making the filenames of photos I’d taken over the last decade consistent), and was quite pleased. I use the script to this day at home.
Which leads back to the link I shared above, about whether VBA or Python is best for Excel.
And I wrote a lot of VBA code over the past seventeen years for Diamond Comic Distributors to do my job better, more consistently, and more efficiently. Yes, I’m a writer, but I have a surprising amount of spreadsheet work, partly because I’m good with spreadsheets. I spent about two hours this morning doing spreadsheet work. And my VBA Excel code is in excess of 8000 lines. It actually may top 10,000 now, I haven’t looked in a while. Scratch that. I am completely wrong. My Excel code is nearing 15,000 lines. Not all of that is used anymore, but I carry it around like a vestigial organ, just in case. The point is, there’s a lot of code.
The idea of porting some of this code over to Python was tempting. First, Python is portable between systems; I could use it on a Windows system with Excel or a Linux system with LibreOffice. Second, it’s easy to run; someone wouldn’t need to have access to my machine to use my Python code the way they would to use my Excel code. (Getting Excel macros set up on another machine is a royal pain.)
I had visions of my Liquidation List macros — they take a weekly spreadsheet, rearrange it and structure it, and build HTML tables and a data file for the website — and turning them into Python scripts. I would be able to use them at home with LibreOffice — I use Linux Mint Debian Edition at home — or with Excel on my Windows 10 machine at the office. As it is now, when working from home, I either have to log into work remotely and run my macros there, or I have to fiddle with a Windows virtual machine.
Visions of code began dancing in my head…
Alas, no Python at work. A security issue.
My Python script to rename art for work went unused.
But that spreadsheet, with the ISBNs and the UPCs and the titles? I wrote a VBA macro for Excel that takes all of that data and turns it into a Windows batch file, full of rename commands. Directories for each publisher are made, the artwork is renamed and moved to the appropriate folder, then I stage the artwork to the server. All told, it takes about twenty minutes, from start to finish. Graphics then has to manually link the artwork in the database, but everything is organized and named. No trying to find the UPC or the ISBN in a folder of art.
And once I realized I could use batch files to automate things, I wrote another script, this one to automate archiving monthly files instead of doing it by hand. Which also solved a long-running issue I had, but that’s a tale for another time. Maybe.
Maybe I should have pressed on with my plan of learning Python, even though I wouldn’t have been able to use it for work. Maybe.