Display custom document property value in Excel 2007 worksheet cell

Question

I created a program that creates and populates its own document property in an Excel 2007 workbook file. However, I could not show the value of this property in a worksheet cell. In Word 2007, you can simply select "Insert → Quick Parts → Field ..." and use the DocProperty field to display the value of the custom field in the document. However, I did not find a similar function in Excel 2007.

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

Answer 1

Unfortunately, I believe that you need to use a user-defined function. Add a new VBA module to your book 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 Application.Volatilecauses the cell to be updated at each recount, ensuring that it closes the changes to the document properties.

Answer 2

The equivalent in Excel will be according to the formula, and I do not think that it is possible to extract a document property without code. There are no eigenfunctions for defining document properties. (An alternative could be to store information in the names of books / worksheets that are accessible through the formula)

In VBA you need to create some kind of function:

Public Function CustomProperty(ByVal prop As String)

    CustomProperty = ActiveWorkbook.CustomDocumentProperties(prop)

End Function

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

. ; . , CustomProperty . recalc. , , , , , , .

Answer 3

, . - . "B1" "Project_Number". " ", "". . " ", "".

cerdit, : http://pdmadmin.com/2012/03/displaying-custom-property-values-in-excel-using-a-named-range/

Answer 4

, [first cell in the]. ; , , .

, , , .

Answer 5

SharePoint ( ):

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

Answer 6

, . , .

, , , , , , "" :

=yourPropertyGettingFunctionName(PropertyName)

PropertyName , / , .

( ) :

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

:

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

, Application.Volatile .

: , Excel, , , , Excel , , - , Excel .

SubVersion . "update" SVN , , , excel .

, , , , , , .

/s. , , , :

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

. (, "" , ), , , :

=Author

"" "A1" "" , .

. , .