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

Select the cell you want to extract Rename the cell to some useful. From "B1" to "Project_Number". Open "Advance Properties" click the "Custom" tab. Enter a name for the new property. click "Link to content" the select the cell name from the "Value" pull down list.

I wish i could take cerdit but I found the answer online: http://pdmadmin.com/2012/03/displaying-custom-property-values-in-excel-using-a-named-range/

Answer 4

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 5

I have experienced the same issues other people have. So I will try to comprehensively cover how I addressed it.

First of all, you have no other option than writing a function meant to get whatever you put in a custom or built-in property and make the "problem" cell to point at it this way:

=yourPropertyGettingFunctionName(PropertyName)

PropertyName being a string referring to the name of the custom/built-in property whose value you want to be shown in the cell.

The function could be written (as formerly suggested) as:

Public Function StdProp(ByVal sPropName As String) As String
    Application.Volatile
    StdProp = ActiveWorkbook.BuiltinDocumentProperties(sPropName).Value
End Function

for a built-in property, or as:

Public Function UsrProp(ByVal sPropName As String) As String
    Application.Volatile
    On Error GoTo UndefinedProp
    UsrProp = ActiveWorkbook.CustomDocumentProperties(sPropName)
    GoTo Exit
UndefinedProp:
    UsrProp  = "n/a"
Exit:
End Function

As already mentioned, including Application.Volatile will allow for a semi-automatic cell contents update.

However, this poses a problem on its own: whenever you open your Excel file, all the cells using such a relationship will get updated and, by the time you exit the file, Excel will ask you for your permission to update it, no matter if you did introduce any change on it or not, because Excel itself did.

In my development group, we use SubVersion as a version control system. In case you inadvertently hit "update" on exit, SVN will notice it and next time you want to commit your changes, the excel file will be included in the pack.

So I decided to use everything at hand to do whatever I needed and avoid, at the same time, this self-update effect I didn't want.

That means using named ranges in combination with property accessing function/s. Given the fact I can't expect old files to have provision for my new needs, I wrote this function:

Private Function RangeAssign(sRange As String, sValue As String) As Integer
Dim rDest As Range
    If RangeCheck(sRange) Then
        Set rDest = Range(sRange)
    Else
        Set rDest = Application.InputBox(sMsg + vbCrLf + vbCrLf + _
            "Please, select a cell to get" + vbCrLf + _
            "the name " + sRange + " assigned", sCopyRight, Type:=8)
        rDest.Name = sRange
    End If

    rDest.Cells(1, 1).NumberFormat = "@"
    rDest.Cells(1, 1).Value = sValue

    RangeAssign = True

End Function

It allows for a proper selection of the destination cell. When assigning values to a property (let's say "Author", which happens to be a built-in one), I also update the value stored in the named range, and can write in a cell:

=Author

if I happen to have defined a range named "Author" and filled its "A1" cell with the value for built-in property "Author", which I need to have updated for our own external tracking purposes.

This all didn't happen overnight. I hope it can be of some help.

Answer 6

I used this for extracting the SharePoint properties (based on Martin's answer):

Public Function DocumentProperty(Property As String)
    Application.Volatile
    On Error GoTo NoDocumentPropertyDefined

    DocumentProperty = ActiveWorkbook.ContentTypeProperties(Property).Value
Exit Function

NoDocumentPropertyDefined:
    DocumentProperty = CVErr(xlErrValue)
End Function