Trying to read CustomDocumentProperties of an open 2007 AddIn

[Nick H]

Here's a puzzle...

I created a version checking tool in Excel 2003 to keep tabs on the
Workbooks and AddIns of a large distributed solution I've developed.
Having recently upgraded to 2007 and converted the various files to
xlsm, xlam and xlsb, this checker no longer works consistently for
AddIns.

Within my AppEventHandler (thanks Bullen, Bovey & Green) ,it seems
that the WorkbookOpen event is unaffected (gsENGINE_NAME is the name
of an AddIn with extension .xlam)...

Private Sub mxlApp_WorkbookOpen(ByVal Wb As Workbook)
If UpdateInProgress Then Exit Sub

If Wb.Name = gsENGINE_NAME Or Wb.Name Like "*" & gsFOIBLES_TAIL &
gsEXTm Then
Call CheckForUpdates(Wb)
End If
End Sub

....and the called Sub CheckForUpdates, which asks for a Workbook as
the argument, processes the passed AddIn fine...

Public Sub CheckForUpdates(ByVal wbk As Workbook)
Dim OldVersion As Boolean
Dim ClientVersion As String
Dim ServerVersion As String
Dim msg As String
Dim Resp As Variant

On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = False
UpdateInProgress = True

If Not wbk Is Nothing Then
' Check Engine version against Versions.ini
ClientVersion = wbk.CustomDocumentProperties("Version").Value
ServerVersion = GetVersionIniFileValue("AddIns", "Engine")
If ClientVersion <> ServerVersion Then
msg = "There is an AddIn update available." & vbCrLf &
vbCrLf
msg = msg & "Your version: " & ClientVersion & vbCrLf
msg = msg & "New Version: " & ServerVersion & vbCrLf &
vbCrLf
msg = msg & "Would you like to update now?"

Resp = MsgBox(msg, vbQuestion + vbYesNo, "C3: Update
Available")

'etc.

HOWEVER, if I wish to loop through the 'open' Workbooks it's a
different story (this used to work in 2003)...

For Each wbk In Application.Workbooks
If wbk.Name = gsENGINE_NAME Then
Call CheckForUpdates(wbk)
End If
Next wbk

....the loop completes without finding the open AddIn and if I change
the code to this...

For Each AdIn In Application.AddIns
If AdIn.Name = gsENGINE_NAME Then
Call CheckForAddInUpdates(AdIn)
End If
Next AdIn

....it finds the AddIn but then I can't get at its
CustomDocumentProperties!

Has anyone else encountered/solved this? How do I either get Excel
2007 to treat an AddIn as a generic Workbook type, the way the
WorkbookOpen event does or, failing that, how do I get at the
CustomDocumentProperties of an AddIn?

Br, Nick

 

[Gary Brown]

Here's a snippet I've used to get custom document properties data...

For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
Cells(iRow, 1) = "Custom"
Cells(iRow, 2) = .name
Cells(iRow, 3) = .value
End With
iRow = iRow + 1
Next

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown