Macros for the Masses

The past few days I've been automating some chart creation activities using Word and Excel VBA. It was kinda cool. I wrote the macro in Word and added a reference to Excel 12 library in the Word's VBA editor (Alt-F11 -> Tools -> References -> Microsoft Excel 12.0 Object Library).

I wrote some nifty Word -> Excel interop to create the charts in excel, then copy/paste them into Word. Data manipulation in Word was not a priority so paste as picture was the solution rather than creating Word Graphs from the data.

Use Project, not Normal

After that, the cool part ended though. I needed the macros to work with Word 2003, but wrote them with Word 2007 - a major boner. Not only that, I found that the default macro location for Word is in Normal.dot and not in the document itself. It makes sense if you are writing macros for only your own benefit, but transferring macros requires them to be located within the document.

To solve the normal.dot problem, I just copy and pasted the text from the default location into the proper location.

But that was the easy part. To fix the other problem, I had to set up a Virtual Machine using VMWare Server and install Microsoft Office 2003. Then I had to recreate the reference to Microsoft Excel 11.0 Object Library.

That solves it, and it looks like the macro works when run in Microsoft Office 2007. However, when I looked at the referenced libraries from Word 2007, it appeared that the reference was upgraded to Excel 12. Safe to assume that if I make a change and save the document, the macros won't work in 2003 anymore because the reference will be wrong. Ugh.

So I need to maintain a purpose built VM to develop macros for office 2003. Gasp. Isn't there an easier way?