Sometimes my Microsoft Word macro coding skills surprise even myself.
Once a month, every month, I create about two dozen files. Word documents, Excel spreadsheets — you name it, I’m creating it. Fortunately, nothing in Powerpoint, because Powerpoint es el Diablo.
Wait. Why am I writing en espanol? No habla espanol, por favor.
This is your brain. This is your brain after Word macro coding. Any questions?
One of the files I generated every month was a spreadsheet. I’d perform some surgery on the spreadsheet, then import it into Word, and do some final clean-up and formatting. All told, we’re talking fifteen minutes from spreadsheet generation to finished document.
However.
About four months ago that changed.
The spreadsheet could still be generated out of the database. Only, now there wasn’t any data on it.
There had been a software update. The database had changed. There was a new field. And things broke.
Then the way that the information was being put into the database changed, and even if the procedure could be made to work again, it wouldn’t actually pull any data.
Suffice it to say, I could still create the document.
However, I had to create it by hand. By going through another document I’d created — a 150-page Word document — and pulling the information out of that. That takes about an hour and a half. Compared to the fifteen minutes it had taken originally. A six-fold increase in time, for no good reason.
The information in that 150-page Word document is incredibly regimented. Things go in a certain order, and they’re formatted in a certain way.
What I realized, while on a walk on my lunch break, was that I could take that 150-page document, turn it into a table, chop the table, change it back into text, and delete a bunch of extraneous lines, and in so doing produce the document I got from the spreadsheet.
It wouldn’t be exactly the same document. In actuality, it would have a little too much information. However, it would have all the information I needed, and then I could go through the resulting document by hand and delete what I didn’t need.
The 150-page document would be chopped down into a specially-formatted 15-page document, and then I would cut that down to the 8-page document I needed.
So I set to work coding.
It took about three hours to write — and I still have the documentation inside the routine to write that explains what it does and why it does it — and it works.
The first routine was almost perfect. I made some code tweaks, and produced a macro that does precisely what I want, that takes the massive document and chops it into the much smaller document that I need.
It’s not the speediest of macros. It takes a good twenty minutes to run, but that’s the nature of the beast — Microsoft Word is good at many things, but it’s table functionality leaves much to be desired, especially on large tables. Taking a 150-page document and turning it into an eleven column table takes time.
There are some odds-and-ends to clean up with it tomorrow. Besides the documentation, I want to encode what “Normal” should be into the macro, because the “Normal” used in the original document isn’t the “Normal” I want the final document in.
Tomorrow, once I’m finished with the odds-and-ends, I’ll put it through a test run on last month’s files. All told, I think it will take about half an hour — between running the macro and deleting the extraneous information — to create the document. A far cry from the recent hour and a half it’s taken.