Display custom document property value in Excel 2007 worksheet cell


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.

Best Solution

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)
  On Error GoTo NoDocumentPropertyDefined
  DocumentProperty = ActiveWorkbook.BuiltinDocumentProperties(Property)
  Exit Function
  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.

Related Question