Inserting OLE properties in Microsoft Excel

Note:   Microsoft Office integration must be enabled in your organisation to utilise this functionality.

You can insert Objective metadata into a Microsoft Excel worksheet as shown in the following example:

You can add the following formulae into Microsoft Excel spreadsheets to insert the desired Objective OLE field:

  • GetCustomPropertyText("Objective-User")
  • GetCustomPropertyDate("Objective-CreationDate")
  • GetPropertyAuthor()
  • GetDocumentPropertyText("Author")
  • GetDocumentPropertyText("Company")
  • GetDocumentPropertyText("Creation Date")
  • GetDocumentPropertyDate("Creation Date")

However, in order for this to function you must add one or more functions to a new VBA module as shown below. The VBA code can be saved in the workbook only or through building and deploying an Excel Add-in (XLA) for the current user or all users.

http://msdn.microsoft.com/en-us/library/aa730920.aspx

Excel VBA Functions:

Function GetCustomPropertyText(propertyName As String) As String
GetCustomPropertyText = ActiveWorkbook().CustomDocumentProperties.Item(propertyName)
End Function
Function GetCustomPropertyDate(propertyName As String) As Date
GetCustomPropertyDate = ActiveWorkbook().CustomDocumentProperties.Item(propertyName)
End Function
Function GetPropertyAuthor() As String
GetPropertyAuthor = ActiveWorkbook().Author
End Function
Function GetDocumentPropertyText(propertyName As String) As String
GetDocumentPropertyText = ActiveWorkbook().BuiltinDocumentProperties(propertyName)
End Function
Function GetDocumentPropertyDate(propertyName As String) As Date
GetDocumentPropertyDate = ActiveWorkbook().BuiltinDocumentProperties(propertyName)
End Function

These values do not automatically update if the properties change in Excel. To update the values, recalculate formulas in all cells by pressing Ctrl + Alt + Shift + F9.

See also

Working with Objective OLE properties

Inserting OLE properties in Microsoft Word