I've been working with .NET and Office interoperability for some time now, and, other than a few minor hiccups here and there, things have generally been very smooth.

But a colleague recently ran into a problem that required a good deal of hunting to resolve. Long story short, even though .NET interoperability with Office objects is virtually foolproof, releasing your references has some hidden dangers lurking amongst the weeds.

As a general rule, it's safe to let the .NET garbage collector (GC) do it's thing while your application is running.

BUT, when you're app shuts down, you need to take a few extra steps to make sure everything's cleaned up before quitting.

The details are laid on in this article on MSDN. It's from 2005, but, from what I can tell, it's just as applicable now as it was then.

Essentially, the trick boils down to this:

When you're preparing to shut your application down, you need to make SURE you've released all your references to any Office objects. This means setting all your various references to Nothing, or, more typically, letting the GC do its thing.

Unfortunately, the GC doesn't necessarily do its thing during a shutdown, so you have to force the issue. And that requires code essentially like this:

WordApp.Quit() 
WordApp = Nothing 
GC.Collect() 
GC.WaitForPendingFinalizers() 
GC.Collect() 
GC.WaitForPendingFinalizers() 

The article noted above doesn't really go into why it's necessary to call Collect and WaitForPendingFinalizers twice, but it does appear to be required. I'm guessing it's due to the way the Finalizer sweeps object references; it could be possible for it to release objects in a certain order, causing it to skip some objects. But that's just a guess.

And as a final note, if your .NET code uses any COM interoperability, it might be a good idea to do some final cleanup along these lines as well.

For today's blog entry, I'm going to delve into a bit of Word esoterica that, if you're into this sort of thing, you might find fascinating, and if you're not, well, you have been warned .

If a document is changed in Word, you'd expect to get a prompt when you close the document, wouldn't you?

Well, that's apparently just expecting too much, my friend.

And if you were to load a document programmatically and simply iterate through it, without changing anything, you'd expect that the document wouldn't actually change, wouldn't you?

Well, that's also… oh, you get the picture.

It appears to be possible in Word, without programming, to create a header that doesn't actually exist, or rather, that exists but is completely empty.

If you then iterate through the headers of the document, retrieving the Range of each, the act of simply "retrieving" the range, not actually modifying it, will cause the header to be modified, inserting a paragraph mark where there was none before.

This can effectively shift the entire document down, since, in the original document there was no "paragraph" in the header, but now there is.

Even more interesting, this modification isn't logged internally by Word, so if you were to close the document, you get no prompt that a change has been made, and from what I can tell, the change isn't actually saved, although it's very visible on-screen.

You can test this by creating a document with a null header, then, adding this VBA code and running it:

Sub Test()
   Dim sct As Section
   Dim r As Range
   Dim hdr As HeaderFooter
   
   For Each sct In Me.Sections
      For Each hdr In sct.Headers
         Set r = hdr.Range
      Next
   Next
End Sub

How do you create a document with a null header? To be perfectly honest, neither I nor a Microsoft Tech could figure that one out. Please let me know if you can.

But what I did discover was that the situation yielded something you could detect.

Essentially, the problem is this:

Each SECTION object in Word has a PAGESETUP object associated with it.

And each PAGESETUP object has an associated HEADERDISTANCE and TOPMARGIN property. It also has FOOTERDISTANCE and BOTTOMMARGIN properties, but I'll only deal with headers here. Footers are handled in exactly the same way.

In a document's section, if the HEADERDISTANCE is ever >= the TOPMARGIN, any changes in the size of the header have the potential for "spilling out" over the limit of the margin and pushing the content of the section body down.

I say potential, because if the header doesn't contain anything, or if what it contains is not enough to push the bottom of the header past the TOPMARGIN, everything will stay put and all will be well.

In any case, right before you save the document, you simply need to iterate through all the headers in all the sections of the document and test if they're empty. If they are, you force the HeaderDistance back to half of the TopMargin.

Section.PageSetup.HeaderDistance = Section.PageSetup.TopMargin / 2

Why half? It essentially doesn't matter what it is, as long as it's less than the TopMargin.

But what makes up an "empty" header?

Once you get the Range of the header

set Range = Header.Range

Make sure it's not a linked header

If Range.LinkToPrevious then {it's linked so don't bother with it}

and then check the character and word counts

If Range.Characters.Count > 1 and Range.Words.Count > 0 and Range.Characters.Count > Range.Words.Count Then
   {this is NOT an empty header}
End If

If it's empty, adjust the HeaderDistance as noted above.

Fun stuff.

I recently had to rearrange some code that dealt with Tracked Changes in a Word document.

After refactoring, I ran through a series of test documents I use to verify things and was surprised to find that one of my primary test documents failed. I got an error that the method I was using (Document.AcceptAllRevisions) wasn't available because the document was protected.

This particular test doc was, in fact, protected, but only with a blank password. It also had 2 sections, the second of which was "protected for form fields". A bit complex, maybe, but certainly nothing horribly bizarre.

The reason the error surprised me is that my app attempts to unprotect any document it finds protected when it loads the file. If the doc is only protected with a blank password, my app will unprotect it and I can usually continue on with no problems.

If the doc is protected with a password, well, c’est la vie. I just throw up my hands and cry mercy!

But the unprotect didn't seem to work anymore. Why?

I started with some googling but got nowhere.

Then I figured it had to be something I had been doing before attempting to AcceptAllRevisions originally, but that I was now doing afterwards. No luck there either.

Ok. It had to be something in the load up process (loading the doc into Word, and setting things up). So I commented out everything but the essentials. Still no luck.

In desperation, I coded up a quick test app to try and replicate the problem outside my app:

Public Sub Main()
   Dim x
   Dim w As Word.Application
   Dim doc As Word.Document
   
   Set w = New Word.Application
   
   w.ScreenUpdating = False
   w.Options.CreateBackup = False
   
   '---- NOTE Pagination must be true for certain RANGE INFO functions to return valid results
   '     It sure can slow some things down though.
   '     TODO investigate this
   w.Options.Pagination = True
   w.Options.BackgroundSave = False
   
   w.DisplayAlerts = wdAlertsNone
   
   w.WordBasic.DisableAutoMacros True
   Set doc = w.Documents.Open("c:\Users\Darin\DeskTop\000-Main-New (2).doc", ReadOnly:=False, AddToRecentFiles:=False, Visible:=True, PassWordDocument:="Blahblah", WritePassWordDocument:="BlahBlah")
   On Error Resume Next
   w.WordBasic.DisableAutoMacros False
   On Error GoTo 0

   doc.Unprotect
   doc.Sections(2).ProtectedForForms = False
   
   doc.TrackRevisions = True
   
   doc.AcceptAllRevisions
      
Cleanup:
   doc.Close SaveChanges:=wdDoNotSaveChanges, RouteDocument:=False
   
   Set doc = Nothing
   w.ScreenUpdating = True
   w.Quit False
   Set w = Nothing
   Exit Sub
   
ErrHandler:
   x = 1
   Resume Cleanup
End Sub

It worked perfectly. Grrr.

To recap, run the above code on it's own, no problems. Run effectively the same sequence in my application, I get a "Document is protected" error when I execute the doc.AcceptAllRevisions line.

Now it's time for hail marys...

I tried playing with the TrackRevisions state, with various incantations against the Unprotect method, even presaving the document. No joy.

In the process, I tried checking the ProtectionType of the document. It was -1 (no protection). Ok. Good. But a bit later, I tried checking the ProtectionType of the Application.ActiveDocument. Protected!

What?!

Then it hit me, when I run my app, it's running as an AddIn within Word. So when I load this document to process, I've actually got 2 copies of the document loaded in Word, the original version, and a COPY of it (with a random filename) that I actually process.

When my test app ran, only one document was loaded in Word, so it HAD to be the active document.

But when I ran my app, even though the AcceptAllRevisions method on a Document object should certainly have nothing to do with some other loaded document (the active document), that does appear to be the case.

Long story short, I force the "active" document to be the document I'm about to AcceptAllRevisions on, like so: (Thanks to David for the heads up that my code snippet below wasn't quite complete!)

Set Doc = {whatever DOCUMENT object you are currently working with}
Set aDoc = App.ActiveDocument 'Get the "active" document, to restore it below
Doc.Activate 'Activate the doc you're working with
Doc.AcceptAllRevisions

'And finally, reactivate the originally active doc (if this is appropriate for you)
If Not aDoc Is Nothing Then aDoc.Activate

I originally wrote about digital signatures in Office documents way back here, so check there for more information. But I just stumbled across something with Word 2007 and document signing (including signing templates) that had me scratching my head for a minute.

Word, in it's shiny new 2007 skin, now has a nifty little feature to add a signature to a document (or template) right on the...um... what they hell you do call this button?

Anyway, under "Prepare", you'll see this:

The Add a Digital Signature lets you sign the document right there. Which is great.

Except for one thing.  That signature is not the same as this one:

The former actually signs the document, whereas the latter signs the VBA code contained in the document.

If you don't believe me, sign a document using the Prepare menu item, then check the signature using the VBA/Tools/Digital Signature menu item. Then sigh and weep.

So, what does that matter, you ask?

Well, in terms of checking the validity of macro code in a document, from what I can tell so far, the signature on the document isn't checked, only the signature on the VBA code. From a macro/VBA standpoint, signing the document is pretty useless.

I'm still hunting for a way to automate the signing of DOC and DOT files (such as the SIGNTOOL.EXE utility for signing DLL's and EXE's). That would make the whole process much more convenient, not to mention enabling it to be built into a normal build process.

Encryption is an unbelievably complicated topic and I'm only now beginning to get comfortable with digital signatures in Office documents.

The Good News is that in Office 2007, other than the fact that the menu locations for checking signatures has changed, most everything about digital signatures has remained the same as in Office 2003, and, from what I can tell, things are compatible all the way back to Office 2000.

The Bad News is that it's still complicated as hell to deal with digital signatures.

I’ll try to cover a few of the bigger bits I’ve come up against while digging through digital signatures in Office (and in particular, in Word).

Signing a DOT file

The first thing you must do when getting ready to sign a template is to manually enable timestamping. Ok, technically, you don't have to do this, but if you don't timestamp the signature, when the certificate used to sign the template expires, so will the signature, and poof, your template will no longer be signed. Depending on how the user’s Word Is configured, that may mean that your template now fails to load.

You enable timestamping by adding a few entries to the registry. Office doesn't provide a way to do this via the UI (even in 2007), so it's all regedit (or run the REG script below).

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]
"TimeStampURL"="http://timestamp.verisign.com/scripts/timstamp.dll"
"TimeStampRetryCount"=dword:00000005
"TimeStampRetryDelay"=dword:00000005

Once you've turned timestamping on, you need to verify that you have a digital certificate loaded that can be used to sign templates.

Click the Start button and RUN, enter MMC and press Enter. This should bring up an empty Management Console.

Click FILE, Add/Remove Snap-in and find the Certificates snap in, then load it up.

When it asks for what type of certificates to manage, select the computer account.

Next, it should ask about managing the local computer account or a remote computer account. You’ll normally want to only deal with the Local Computer.

Once you get the certificate manager loaded, expand the personal certificates. Generally speaking, this is where certificates purchased through online vendors (like Verisign) will end up when you download them.

Now, right click the certificate and choose Properties.

You should see code signing in the box as shown. I’m not an expert on the different types of certificates, so it may be possible to sign documents WITHOUT a code signing certificate. Best to talk with someone at your vendor for details on that. One definite benefit of a code signing cert is that you can use it to sign DLLs, and EXEs, as well as templates.

Anyway, at this point, you can be pretty sure the cert is appropriate for signing templates.

One final note: It is often a good idea to export the signature so you can use it via the command line etc. The PFX (Personal Information Exchange) file format is good for this, because it contains both the public and private keys for the certificate. The bad news is you want to protect the PFX file as much as possible, because if someone obtains it and the password used to create it, they would be able to sign documents as if they were you.

You can export to a PFX file by right clicking the certificate entry (in the middle list), selecting All Tasks, then Export.
Indicate that you want to export the private key (which will require a password), and then make sure you select the PFX output option.

Verifying the Signature

Verifying that you actually got the Digital Signature correct is one of the more frustrating parts of working with digital signatures in Office.

You'd think you'd be able to verify the signature via the Tools/Digital Signatures menu in the VBA editor screen:

If you get this far, you know that the signature was signed. You can check the valid dates on the certificate as well.

However, this screen does NOT show the date the signature was timestamped and that, unfortunately, is a key piece of information you need to verify the signature. In fact, although this screen purports to be showing the digital signature, this is really just information on the certificate used to create the signature.

Verifying the TimeStamp

To actually get information about the signature, and specifically the timestamp, you have to jump through a few hoops.
First, make sure that the template IS NOT in any of the autoload places (most commonly the Word STARTUP folder or the Excel XLSTART folder).

Next, make sure that security is set such that unsigned templates won't automatically load without prompting.

Once you've done that, if you put the signed template, say, on the desktop and load it by holding down the SHIFT key, right clicking, and selecting OPEN, you should get the message bar indicating that there are security warnings.

Click the Options button and you should get a Security Alerts Window:

Click the Show Signature Details link to see the actual details of the signature itself:

Notice the title of this dialog is actually "Digital Signature Details", not "Certificate Details". And the Signing Time is indicated here, showing that, in fact, this digital signature was timestamped.

One final note. The Office 2007 and 2003 object model (and XP, but not Office 2000), contains properties for enumerating and retrieving information on document and template signatures. Specifically, the DOCUMENT object now sports a SignatureSet property, which is a collection of signatures on the document. From there, you can navigate through the object model to retrieve all the signatures on the document, as well as details (including the signing time) of those signatures.

However, there are significant differences between the models in 2007 vs 2003, so any code written to utilize those objects would need to accommodate those differences.

The above steps work similarly in all versions of Office from 2000 to 2007. I’ll try to capture screenshots of the process in Word 2000 at some point, to illustrate the differences.

Related links

Office 2000 Macro Security

Dealing with attachments in Outlook is a bit like entering an Ultimate Fighting match against a professional half-octopus, half-eel. It really doesn't matter what style of fighting you choose, the octo-eel is guaranteed to take you down more than once in the match, and there's really no telling whether you have a chance at winning.

An app that I'm working on deals with attachments in Outlook, more specifically, it deals with Office format attachments, so we're talking about DOC, XLS, and PPT files (and about 2 dozen other *X and *M formats introduced in Office 07).

Anything else (like BMPs, TXT files, etc) are supposed to pass on through like yesterday's iceberg salad.

However, astute customers noticed that, in some cases, the order of the attachments could change at times. If you had numerous attachments to your email, and you intended on them being in a particular order, that might not go over well.

Now, you might think, with such a nicely laid out object model as Outlook's, that preserving the order of attachments in a message would be trivial. Hell, you might even thing you could simply replace one attachment with another, simply by using a replace method or somesuch.

And you'd be wrong.

There's no replace method, unlike GroupWise. And the ATTACHMENTS.ADD method doesn't allow you to specify an ordinal. It does allow you to specify a position, but that value is only used to indicate the character position of the attachment in an RTF format email. If the email format is plain text, or HTML, the position isn't used at all.

Then there's this jewel from the MSDN documentation on the add method.

To ensure consistent results, always save an item before adding or removing objects in the Attachments collection of the item.

Special.

Add to all of that the fact that Word can also be used as the email editor (albeit in approximately the same capacity as the built-in Outlook RTF editor from what I can tell), and you have a whole lot of arms to fight with on this octo-eel.

The bottom line however, turns out to be relatively simple.

1. First, you have to make a single pass through all the attachments in a message and for every one you plan on removing, you need to add the new one. In addition, for those that you don't plan on removing, you have to extract them, then add them again anyway, in the same order as they already exist.
   This will cause the email to end up with a double set of attachments; the originals, and all the new ones, plus a second copy of any original attachments you didn't necessarily want to mess with but have to anyway.
2. Next, make another pass through all the attachments, removing all of the original attachments, but leaving everything else. Now, the email will contain the proper list of attachments, both new and untouched originals, in the original order, at least for plain text and HTML formats.
3. For Rich Text, there's one more trick....
4. When you add the new attachments (or re-add those attachments you intend to pass through, in step 1 above), retrieve the POSITION of the attachment first.
   If it's 0, make sure you add the new attachment WITHOUT specifying a position argument. Specifying a 0 for the position will only mess things up.
   If it's NOT 0, however, add the new element with a position argument of the original POSITION + 1

It sounds more complicated than it is.

If you get any part of it wrong, you'll know pretty quick. Usually, the attachments will end up in the sent email reversed in order. Worse case, some will end up missing.

One final trick. Testing all this can be a real pain, but you can make it a little easier.

Set up Outlook with POP accounts only (don't use Exchange for this). Then set it to NOT send email on startup:

Anything you send will end up in the outbox, where you can examine it, use it for debugging, drag it back to the inbox to attempt a resend, or delete it. And from what I can tell, whatever ends up in the outbox is what would go. There's no additional processing done after that.

Plus, you don't even have to use a legitimate TO address, anything that looks like an email address will work.

Believe it or not, I actually like Outlook, despite it's, uh, quirks? It's been my email system since about 97. I've never had a corrupted PST file, never lost an email (that I didn't unintentionally delete with Shift-Del), and never gotten an email-born virus.

Now, excuse me, I have some serious knocking to attend to. 

I'm finishing up my little Signature Enhancement Utility for Outlook and had finally gotten the Media Center 12 "Currently listening to" functions operational (This is just a minor feature I've seen popular on website blogs and forums, where the tag line contains not only the author's name but what they are currently listening to, if anything, nifty and fun, but not in the least practical).

I figured I'd go ahead and try to support Windows Media Player 11 (and hopefully earlier versions) as well.

Basically, the idea is to grab a reference to the running instance, interrogate it as to the "playing" state and, if it's playing or paused, retrieve the name, album, artist, etc info on the playing track and make it available as replaceable fields in the signature.

With Media Center, it was almost trivial:

Set omc = GetObject(, "MediaJukebox Application")
If not omc is nothing Then
   '---- it's running
   ' if it's not running, they can't be playing any music
   With omc
   Select Case .GetPlayback.State
      Case PLAYSTATE_PAUSED, PLAYSTATE_PLAYING
      '---- Media center info is available
      ps = .GetCurPlaylist.Position
      CurTrackTitle$ = .GetCurPlaylist.GetFile(ps).Name
      etc...

Obviously, if the GETOBJECT fails to return anything, Media Center isn't currently running so the user can't be listening to anything.

Three hours of Googling later, plus tons of experimentation and I'm not even an inch closer to getting this working for Media Player.

Using ROTView(the Running Object Table viewer, comes with various installations of Visual Studio), it does appear that WMP registers "something" with the ROT, which I'd think would be accessible by VB's GetObject().

Alas, "Windows Media Player", "WindowsMediaPlayer", "MediaPlayer.MediaPlayer", and on and on, all came up empty.

I scoured the registry for anything that even remotely looked like the moniker of a WMP registration with the ROT and everything I tried also came up empty. I'm sure it's another case of knowing the magic password, but so far, it appears to be a tad more involved than Speak, friend, and enter.

So, for now, looks like I'll have to rely on the FunPack for support of a limited set of attributes of the currently playing song in WMP. Apparently, for ITunes, you can use this plugin to accomplish the same thing, though I don't use ITunes and probably won't bother with testing that.

If anyone's ever had any success with accessing the running instance of Media Player, I'd love to hear about it!

Ran into something very interesting with Word, Templates and the Word Object Model today.

I have a Word Addin that creates a Word Template on the fly and loads it when Word is loading up.

The template is used to save any modifications (via the Application.CustomizationContext property) to the Word Toolbar that I have to make while running, which is pretty standard stuff for a Word COM Addin.

Under normal circumstances, this arrangement works quite well. But today, I had a client report a problem where when they'd close Word, they'd get a pop up message that Word failed to save my template, because some other process has it open. Further investigation showed that 2 or more instances of Word were being loaded (in the background by another application), and the multiple instances of Word appeared to be what was causing the issue.

Now, most anyone who's had to mess with supporting Word as a programmability platform knows it's not a good scene to encourage multiple instances of Word. It just doesn't work out all that well. But here I was, and the other app was a necessary one for this client, so uninstalling it was not an option.

After quite a bit of poking around, it turns out that Word appears to have a problem, or at the very least, an oversight.

The Word Object Model supports an Application.DisplayAlerts property, that is supposed to turn off message boxes, but it fails to do so in the case when you obtain a Template object and then use the Save method on it.

So even if you're trapping errors on the save, intending to handle them through your code, and even if you've turned off alerts, using the above property, Word will still pop up that warning box. Since my template is a completely background piece of functionality, the last thing I want users seeing is warnings about Word being unable to save it.

And here's the kicker. Since you can't necessarily know whether Word will be successful at saving the template beforehand, there is no way you can determine whether or not you should even attempt to save the template. A classic catch-22.

In the end, I was able to work around this, er, unique, behavior by:

• during the Word/Addin startup process...
• Check if my template exists
• If it doesn't, create it and set a flag indicating that it's safe to save (usually during the Word shutdown process)
• If the template does exist, check if the file is writable (by attempting to open it with write access)
• If that fails, the template is already opened (likely by another copy of Word) so set the flag indicating to NOT attempt to save the template from this instance of Word.
• If the file writable check succeeds, the template is writable, indicating that nothing else has it open, so set the flag to indicate that it should be safe to save the template.

It's not ideal, because, under some very peculiar circumstances, it still might be possible for Word to fail to save the template, and subsequently pop up that warning box. But such is the joy of the Word Object Model!

On a lark a few days ago, I decided I'd like to have random rotating quotes appended to my outgoing emails. I use GMail for mail preview (and access when I'm away from my desk), but usually, I use Outlook when I'm at my desk.

Supporting random email signatures in GMail is a topic for another day, and I've honestly not even investigated it at all.

But Outlook. Surely, I thought there must be a free little utility to do that out there.

Well there are a few, but most aren't free, and the few that are, aren't particularly well built from what I can tell.

The one free one I did see that seemed to hold promise was QLiner. After a quick install, and then converting some quotes to a plaintext file, I fired it up and set things up.

It works, but it seems finicky to me. For one, it doesn't actually template out the signature, so much as it completely replaces the existing signature with a rebuilt version (with the new quote) on a configurable timeout. So if you sent 3 messages within 5 minutes, and the timeout was set to 10 minutes, it's likely all 3 messages will have the same quote.

Plus, it seemed to just "stop working" after a time. I never could see a pattern to this, but, invariably, after a few days, I'd notice that the signature wasn't changing anymore. Stopping and restarting the program fixed it each time, but that's not the hallmark of a solid app.

In the end, I uninstalled it, and I believe I'm going to throw my hat in the ring on this one.

I'm calling it Sig-Licious. The idea is that it is a simple COM Outlook addin that monitors for new email creation events, intercepts it, and parses and replaces keywords in the signature with specific bits of info.

For instance, {Date}, {Quote}, {QuoteAuthor}, {CurrentTrack} (ie what piece of music you're listening to right now, if any), etc, etc.

Any thoughts on what other variables might be nice? To totally geek it up, how bout any ol' Environment Var? Or the content of a performance counter at the time? Or maybe your CPU fan speed? I'm thinking various Active Directory fields might be nice, if available. Or maybe info from arbitrary Text files? Or maybe info from arbitrary posts you store in specific folders within Outlook proper.

I'll post something when I've got it a bit farther than just messageboxes .

Or stop me before I get too far and tell me the URL of that nifty app you've found that can do all this. Please!

Update

I found a few more apps out there for this. First, ADOLSign is 129$! For what is essentially a search and replace tool. Jeez.

Then there's Exclaimer. It's even more expensive, but does a lot more. For an enterprise setup, I could see spending a few bucks for this sort of functionality, but for your average joe, like me? Um, no.

I also just ran across Symprex Mail Signature Manager. It's freakin' expensive and you can only get a min 10 user license, and it appears to require Echange, but if you need something like that, it looks pretty complete.

And finally, Bells and Whistles. It's also basically a glorified macro search and replace. 29$. Better, but still, 29$ for this?

So far, nothing for the loan gunman looking for nifty sigs. I think I'll continue on with my little addin.

Had an issue today where some automation code (basically VB that instantiates Word, Excel, or PowerPoint objects), started failing inexplicably.

The only thing the user could think of is that he'd just installed the lastest McAfee AntiVirus.

So, I tried a little test, opened Excel, opened the VB editor in it, then entered:

Sub Main
   Dim X as object
   Set x = CreateObject("Word.Application")
End sub

And that failed too!

After a few googles, I came up with this.

Appearently, McAfee includes a feature called HAWK (Hostle Activity Watch Kernel), that monitors things like CreateObject.

And the fun part is that even if you disable McAfee, HAWK still runs in the background. You have to turn it off seperately in the McAfee UI.

Yet another reason to use NOD32.

By now, I suspect the Office 2007 Ribbon is a bit old news.

From a developer's point of view, it's relatively easy to customize the ribbon via add-in code, which is really nice. And since there seems to be no dearth of examples on doing so, I won't be wandering down that path.

For Word, Excel, and PowerPoint, things are pretty straightforward, because, as far as I can tell, there's only one Ribbon for them.

But in Outlook, things are more complicated. The main Outlook window doesn't even have a ribbon. But the sole parameter of the main entry callback for customizing the ribbon is:

Public function GetCustomUI(ByVal RibbonID as string) as string

so what are the possible RibbonIDs?

After a good bit of searching, I found the following table in the VSTO3CTPTutorial.doc file that identifies them all:

BTW, for those that are interested, in Word, the RibbonID is always Microsoft.Word.Document

Similiarly, in Excel, it's Microsoft.Excel.Workbook

and in PowerPoint, it's Microsoft.PowerPoint.Presentation

I wasn't able to find that info published anywhere. I suppose it's technically irrelevant, but still...

I'm a bit of a toolhound, both of the physical variety (like screwdrivers, bitstops, routers and mitre saws) and of the more ethereal (clipboard loggers and macro recorders). Tools make the fun things even more fun, and the wretched things not quite so onerous.

And one really onerous thing for me is backups. They're a pain, and it seems it's always impossible to find what you need when you need it. I've got a pile of QIC-60 and QIC-80 tapes that there's no way I could restore even if I wanted to (note to self, burn them in the next trash fire...)

Anyway, I went on a search some time ago for a decent backup utility. Something free, or close, that was flexible enough for what I needed, simple enough that I'd actually use it, capable enough to make it worthwhile and fast enough to not get in the way.

I believe my search ended with FileBackPC.

It's a nifty little app that does a nice job of the "copy to a floppy" type of backup. Well, ok, maybe not a floppy these days, but plug in a 500gb USB2.0 removeable harddrive and this app is fantastic.

The good points:

• It backs up specified directories, with wildcards and all sorts of file filters
• It can compress and retain a specified number of "previous versions"
• You can specify sets of folders in "jobs" that can be run independently
• You can set jobs to automatically run, either on a schedule or on "an event" (like plugging in that USB harddrive!)
• It can reconnect to other machines on your network and back up files from the (great for small home offices with a server and several workstations or laptops).
• It can even run batch files and scripts to automatically execute processes before backing up the results of those processes. For instance, I have an item that uses the windows "backup" utility to create a single backup file of the critical system components and my Exchange server data, then I backup that file automatically using FileBackPC.

Off hand, I can't even think of any bad points, other than it's not an open source project.

I think my favorite feature is the backup on an event. I set several jobs up to execute when the X: drive comes online (that's my backup USB drive). So literally all I do now is plug in the USB drive, wait till it finishes the backup, and then unplug it and file it safely away. Slick. And it's got a nice reporting facility too, that makes it easy to see if there were any problems (open files, read errors, whatever).

And, since it's a copy style backup, it's blindingly easy to find that backup file when you need it. Get a couple of USB drives and be doubly safe.

Backup utilities definitely aren't my favorite things, but they're something almost everyone with a PC needs. I know this has probably sounded more like an ad than anything else, but it's not. I'm not getting paid by the FileBack people. This is one of those utilities that I've found so handy, useful and easy to work with, that I just felt like mentioning it.

New to the Excel 2007 object model is the Comments property of the Name object.

The Name object itself has been around since Excel 2000 and possibly before, but the Comment property is a handy new development.

I like Comment properties personally. Any object model representing items such as this would benefit from a property where you can stash whatever info is pertinent. Take, for instance, the venerable tag property of VB controls. Of course, these things can be misused, but then, is there anything that can't be misused in the wrong hands?

Anyway, comments like this are great, but they also represent potentially sensitive information that you might not want to reveal to third parties, especially in this case, because the comments themselves are persisted to the workbook. Excel 2007 has a nifty Document Inspector you can use to remove these comments, which is nice. However, when you run the inspector, it simply says that there were comments detected, would you like to delete them? Nice, but not terribly informative.

Retrieving the comments on a Name is not too hard.

cmt = ExcelApp.ActiveWorkbook.Names(1).Comment                                                   

But, when you go to clear the comment:

ExcelApp.ActiveWorkbook.Names(1).Comment = ""

Nothing happens...

Excel, and Word, and PowerPoint for that matter, all seem to be afflicted by this particular "a blank string doesn't really count" malady in one way or another. Setting the Creation Date, for example, or the Username or UserInitials all seem to have similar problems with being set to a null string.

The solution, though not ideal, isn't too terribly bad either, I suppose.

ExcelApp.ActiveWorkbook.Names(1).Comment = " "

Yes, that's right, just set it to a space, instead of a null string.

It'd be interesting to see the code responsible for implementing these properties, just to see how it could be coded such that a null string is ignored like this. It seems like it just couldn't have been intentional.

And just for the record, passing an actual null char doesn't work either, so it doesn't appear to be a "C string/BSTR related" issue.

For anyone that might not be familiar with the issue yet, Office 12 introduces a rather large number of new file formats.

There are formats that include macros (DOCM, XLSM, etc), formats that explicitly exclude macros (DOCX, XLSX, PPTX), and, for the Excel aficionados out there, a whole new binary format, XLSB.

This new binary format is not the old style XLS format, although both are binary and relatively proprietary. It is, however, based on a ZIP container file, just like the other new Office formats. This is a big difference from the older DOC and XLS files, which were OLE Structured storage files.

Stephane Rodriguez has written an excellent article on Code Project about the new format, with some good info on the old formats too.

Very interesting that the BIN files within the zip container turn out to be OLE Structure Storages almost exactly like what the old formats used, just wrapped up in a ZIP container instead of a Stuctured Storage container.

So much for really simplifying anything.

I was trying to exercise the repurposing functionality in the Office 2007 ribbon today.

Specifically, I wanted to intercept the FileDocumentInspect command on the Office Menu.

Now, I've already added a button to the ribbon, so I added the <commands> section just like all the examples I've found indicate:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <ribbon>
  <tabs>
   <tab idMso="TabHome">
    <group id="MyGroup" label="My Controls" insertAfterMso="GroupClipboard">
     <button id="MyButton" label="Hello, Word!"
      imageMso="HappyFace" onAction="HelloMacro" size="large"/>
    </group>
   </tab>
  </tabs>
 </ribbon>
 <commands>
  <command idMso="FileDocumentInspect" onAction="MySub"/>
 </commands>
</customUI>

Run the app, load up Word, no joy.

Is the example I found based on Beta bits? Have I mistyped a quote somewhere?
So I start hunting.

Eventually, I had what you might call an AHA moment.

If you have ever played with the .NET configuration stuff, you know that one of the infuriating aspects of it is that the XML sections can be "positional", meaning that if you put a particular section after another section, it won't work, but put it BEFORE and all is good. I suppose there might be a rational explanation for this, and for that matter, I suppose there might be a rational reason I'd want two different files to be distinguishable only by case , but sorry, I just don't see it.

Anyway, put the commands section BEFORE the ribbon section and all is right in the world again:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <commands>
  <command idMso="FileDocumentInspect" onAction="MySub"/>
 </commands>
 <ribbon>
  <tabs>
   <tab idMso="TabHome">
    <group id="MyGroup" label="My Controls" insertAfterMso="GroupClipboard">
     <button id="MyButton" label="Hello, Word!"
      imageMso="HappyFace" onAction="HelloMacro" size="large"/>
    </group>
   </tab>
  </tabs>
 </ribbon>
</customUI>

Positionally dependent XML. Gotta love it.

I ran into this several weeks ago and initially blew it off as something I was probably not doing right.

I was trying to add my own button to an existing group on one of the Word 2007 tabs.

Nothing I tried would work. I finally gave up and just created a new, 1 button group with my button in it.

Fine.

Today, I ran across this PDF of a presentation by Ken Getz. On one of the slides towards the end, he makes an offhand comment about not being able to customize an existing group in the Ribbon.

Instead, he goes on, you could hide the existing group, and recreate it entirely, then add your own button(s).

He does not recommend doing so, though, because other addin's might end up wanting to alter the original group and the results would be less that perfect.

So, I guess you just can't.

I just stumbled across a quite informative article about the Office 2007 Document Inspector API.

http://msdn2.microsoft.com/en-us/library/aa338203.aspx

Basically, it's a new API for removing metadata from Office Documents. It's extensable, which is cool.

But it's woefully underpowered as is, and it's only applicable to Office 2007 installations.

At any rate, a decent article on the subject.