A little background

A while ago, I blogged about the Excel 2007 Icon Gallery, which is available from Microsoft right here. It's a useful tool when you're trying to identify the imageMso that you're after, no question... but there's a problem... It's only available in Excel. 🙁

Based on the information in my previous blog post, you'll know that I've also turned Microsoft's workbook into an add-in so that its always available to me. I've made some fair use of it in playing around with Ribbon modifications as well.

Last week I was writing up some stuff with Word, and I found myself flipping back over to Excel to look up the imageMso that I needed. This struck me as kind of silly, as I figured that it should be pretty easy to port this over to Word. I mean, really, that was the whole point behind going to XML and consistent structure, right? So I decided to do it. In fact, I built a version for Word and Powerpoint. Both they and the Excel version are included in the zip file attached to the bottom of this post. I may also add an Access version later, but you'll have to hold on for that one.

Before I started on porting this to Word & Powerpoint though, I took on some comments from Zack Barresse (both from the aforementioned blog post feedback as well as a VBAX post) and decided to implement them as well. I changed the userform a little bit and added Zack's ultimate lazy factor in; having the button automagically copy the imageMso name to the clipboard. Just because I could, I also added the option to close upon copying, as I figured that someone may not always want to copy and exit. The value of the checkbox is stored in the registry, so you only have to set it once.

Here's a picture of the new userform in action:

So then came the fun part...

Excel

Now, for me Excel was pretty easy as I've built more add-ins than I can remember. In this case even easier, as I was only really adjusting work someone else already did. If you've never installed an Excel add-in before though, you simply:

• Download the zip file and extract it to your computer somewhere.
• I'd suggest copying the Office2007IconsGallery.xlam file from that location to store it in the addins directory which can be found at:
  • Vista --> C:\Users\Username\AppData\Roaming\Microsoft\AddIns
  • XP --> C:\Documents and Settings\Username\Application Data\Microsoft\AddIns
• Open Excel 2007
• From the Office menu, choose Excel Options|Add-Ins|Go...
• Check the box next to Office2007IconsGallery, then OK

Now, check the Developer tab for this:

No big secret here. Just click a gallery, select an image, and you'll get the userform that I displayed above.

Word

Now Word was a little more entertaining than Excel, as I'd never built and addin for Word.

Here's the process I used to convert it:

• In Excel, I exported the userform and code module to the desktop
• I created a new Word .dotm (Macro enabled template) file, and imported the userform and code module
• After saving the file and closing both Word and Excel, I opened the Excel add-in in the CustomUI editor
• I copied all the XML code from the Excel add-in, and closed the file
• Finally, I opened up the Word dotm file in the CustomUI editor, pasted all the XML, saved it and closed it.

Honestly, that was the easy part. As soon as I opened the file again, my group was there and it worked. The problem now was deployment...

I'd just assumed that you could dump it in to Normal.dotm, and away you go. While that works, it's a really bad idea, I found out, as the XML (at a minimum) goes with whatever documents that you create off of the normal template. That's not really cool, as I didn't really want to send that modification to everyone, so I needed a different way.

Now, Word doesn't use Add-ins, per se, but rather a form called a Global Template. While I'd heard of these, I'd never really built one in the past, as I've never created any Word utilities that I needed to share. Basically, to make your template into a global template, you just drop it in Word's STARTUP directory... that's it. In all honesty, this was a bit of a surprise, as if you do this in Excel, the file opens and is visible. Word essentially treats all its templates stored there as add-ins, so there you go.

So to recap, if you want to use the Word version of the file:

• Download the zip file and extract it to your computer somewhere.
• Copy the Office2007IconsGallery.dotm file to:
  • Vista --> C:\Users\Username\AppData\Roaming\Microsoft\Word\STARTUP
  • XP --> C:\Documents and Settings\Username\Application Data\Microsoft\Word\STARTUP
• Open Word, and it's already there on the Developer tab

There is no checking of boxes required to get this to load or anything. It will just be there when you start Word up, and none of your new documents will inherit its XML code base. 🙂

Powerpoint

Probably the most mystifying of the applications to work with in this regard is Powerpoint.

Here's what I did first, much to my chagrin...

• In Excel, I exported the userform and code module to the desktop
• I opened Powerpoint, imported the userform and code module into the file and then saved it as a Macro Enabled Template (ppam) file
• After saving the file and closing both Powerpoint and Excel, I opened the Excel add-in in the CustomUI editor
• I copied all the XML code from the Excel add-in, and closed the file
• Finally, I opened up the Powerpoint ppam file in the CustomUI editor, pasted all the XML, saved it and closed it.
• I opened Powerpoint and tried to install it as an add-in, only to be told it wasn't a valid file

Grr...

I double-clicked the file from Windows Explorer, and it opened up just fine with my customizations. But when I closed Powerpoint and re-opened it, I could not get the add-in to load automatically.

It turns out that you need to add a little code to your add-in to get it to register itself for auto-loading. So armed with this code, I opened up the file, jumped in to the VBE... and it was not there???

So a big note to everyone who is building their first add-in in Powerpoint... SAVE A BACKUP COPY TO A REGULAR FILE FIRST! It looks like once you've converted to an add-in, that's it. One shot, and you're done. So back I went, repeating all the above steps, but this time with a regular Macro Enabled (pptm) file. In addition, I also added the following code to get it to register for auto-loading:

Sub Auto_Open()

' Get a reference to your add-in.

With AddIns(AddIns.Count)

' Create the registry key in HKEY_CURRENT_USER.

.Registered = msoTrue

' Set the AutoLoad value in the registry.

.AutoLoad = msoTrue

' Makes sure that the add-in is loaded.

.Loaded = msoTrue

End With

End Sub

So with the above code in the standard module with the RibbonX callback code, I saved the file (in the pptm format), then saved it as an add-in again. Nothing really seems to happen at that point really except you get prompted for the directory to save it in, of course. (Oh! By the way, Powerpoint Add-ins go in the Add-ins directory.) But once saved, you're back staring at your original file again, with no indication that the add-in ever really loaded. At any rate, I then closed down Powerpoint, browsed to the add-in location in Windows Explorer, and launched it from there. It works like a breeze now.

Fortunately for you, I've included the powerpoint add-in in the zip file as well. To use it:

• Download the zip file and extract it to your computer somewhere.
• Copy the Office2007IconsGallery.ppam file to:
  • Vista --> C:\Users\Username\AppData\Roaming\Microsoft\AddIns
  • XP --> C:\Documents and Settings\Username\Application Data\Microsoft\AddIns
• Double click the file in Windows Explorer, and it will open Powerpoint for you and register the file
• Browse to the Developer tab to see it waiting for you

After you've followed these steps once, you don't have to worry about lauching the file that way again. It's registered to load automagically every time you launch Powerpoint.

Some conclusions

Whether you feel that Microsoft got it right with the Ribbon or not, I think you'd have to agree that deploying an identical UI enhancement across multiple applications would be complicated to say the least. This was actually remarkably easy, in truth. The biggest complication for me was learning the deployment side of the Add-ins/Global Templates. Once done though, it was a piece of cake. I'm actually fairly impressed. 🙂

Downloading the Add-ins

Heard enough? Sorry, just need to say a couple more things here...

• The original XML code in these files was all produced by Microsoft. I have not modified it in any way at all.
• The original userform has been modified by myself to allow copying of the imageMso name, but this was originally Zack Barresse's idea.
• The superior laziness of being able to click a button to have the imageMso copied to the clipboard is definitely a Zack thing as well. I wish I'd thought of how to be quite that lazy, but I didn't. 🙁
• The idea to extend this to Word & Powerpoint, I'll lay claim to, though. 😉

And at long last... here's the file! 🙂

Office 2007 Icons Gallery