Display custom document property value in Excel 2007 worksheet cell

Question

I've created a program that creates and populates a custom document property in an Excel 2007 workbook file. However I haven't been able to show the value of this property in a worksheet cell. In Word 2007 you can just select "Insert -> Quick Parts -> Field..." and use the DocProperty field to show the value of the custom field in a document. However I haven't found a similar function in Excel 2007.

Does anybody know how to display the value of a custom document property in an Excel worksheet cell? I would prefer a solution similar to the Word 2007 solution mentioned above. I rather not use a macro/custom code for this.

Answer 1

The equivalent in Excel would be via formula and I don't think it's possible to extract a document property without code. There are no native functions to pick out document properties. (An alternative could be to store information in workbook/worksheet Names, which ARE accessible via formula)

In VBA you'd have to create a function something like:

Public Function CustomProperty(ByVal prop As String)

    CustomProperty = ActiveWorkbook.CustomDocumentProperties(prop)

End Function

and then call it in a formula with =CustomProperties("PropertyName").

There is another subtle point. Formula dependencies only relate to other cells; this formula depends on a custom property. If you update the custom property a pre-existing formula involving CustomProperty will not be updated automatically. The cell will have to be re-evaluated manually or the entire workbook forced through a recalc. Your best chance would be to make the function volatile, which means the formula would be recalc'd on every cell change -- but this still means you only get an update if a cell has been changed.

Answer 2

Unfortunately I believe you need to use an user defined function. Add a new VBA module to your workbook and add this function:

Function DocumentProperty(Property As String)
  Application.Volatile
  On Error GoTo NoDocumentPropertyDefined
  DocumentProperty = ActiveWorkbook.BuiltinDocumentProperties(Property)
  Exit Function
NoDocumentPropertyDefined:
  DocumentProperty = CVErr(xlErrValue)
End Function

The call to Application.Volatile forces the cell to be updated on each recalculation ensuring that it will pick up changes in the document properties.

Answer 3

You can link a named range to a custom property, but then the custom property reflects the value of the [first cell in the] range. It's effectively read-only; you can change the content of the cell to update the property, but not the other way around.

I know you want to avoid it, but if you want to use the property value in a formula, you'll have to create a custom worksheet function to do so.

Answer 4

Thank you very much! Saludos desde México.