Sometimes my Microsoft Word macro coding skills surprise even myself.<\/p>\n
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.<\/p>\n
Wait. Why am I writing en espanol? No habla espanol, por favor.<\/p>\n
This is your brain. This is your brain after Word macro coding. Any questions?<\/p>\n
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.<\/p>\n
However.<\/p>\n
About four months ago that changed.<\/p>\n
The spreadsheet could still be generated out of the database. Only, now there wasn’t any data on it.<\/p>\n
There had been a software update. The database had changed. There was a new field. And things broke.<\/p>\n
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.<\/p>\n
Suffice it to say, I could still create the document.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
It wouldn’t be exactly the same<\/i> document. In actuality, it would have a little too<\/i> 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.<\/p>\n
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.<\/p>\n
So I set to work coding.<\/p>\n
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.<\/p>\n
The first routine was almost<\/i> 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.<\/p>\n
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<\/i> on large tables. Taking a 150-page document and turning it into an eleven column table takes time<\/i>.<\/p>\n
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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 isContinue reading “On Writing Word Macros”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[229],"tags":[426,4124],"class_list":["post-1789","post","type-post","status-publish","format-standard","hentry","category-work","tag-microsoft-word","tag-work","entry"],"_links":{"self":[{"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=\/wp\/v2\/posts\/1789","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1789"}],"version-history":[{"count":0,"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=\/wp\/v2\/posts\/1789\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1789"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.allyngibson.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}