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

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.

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.