Microsoft Office 2000/Visual Basic Programmer's Guide   

Working with Document Properties

Every file created by a Microsoft Office application supports a set of built-in document properties. In addition, you can add your own custom properties to an Office document either manually or through code. You can use document properties to create, maintain, and track information about an Office document such as when it was created, who the author is, where it is stored, and so on. In addition, when you save an Office document as an HTML file, all of the document properties are written to the HTML file within <XML> tag pairs. This enables you to use document properties to track or index files according to properties you specify, regardless of what format you use to save the file. To see an example of how built-in and custom document properties are saved as HTML, see DocumentProperties.htm in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.

Note   Microsoft Office uses the term "document" to represent any file created by using an Office application.

You can view and set built-in and custom document properties by clicking Properties on the File menu. (In Access, click Database Properties on the File menu.)

Figure 6.5 The Document Properties Dialog Box

The built-in and custom document properties are stored in the shared Office component called the DocumentProperties collection. Each individual property is represented in that collection by a DocumentProperty object.

Document Properties in Microsoft Access, Microsoft FrontPage, and Microsoft Outlook

Access does not use the DocumentProperties collection to store the built-in and custom properties displayed in its Database Properties dialog box. You can access these properties by using Data Access Objects (DAO) in an .mdb-type database. For more information about database properties, search the Microsoft Access Visual Basic Reference Help index for "database properties."

FrontPage also does not use the DocumentProperties collection to store the built-in and custom properties displayed in its Page Properties dialog box (File menu). In FrontPage, built-in and custom properties are stored in the MetaTags and Properties collections of a WebFile object. For more information about working with the FrontPage object model, see Chapter 5, "Working with Office Applications."

Outlook does not provide a Document Properties dialog box from the File menu like the other Office applications do.

You access the DocumentProperties collection by using the BuiltInDocumentProperties and CustomDocumentProperties properties of an Office document. For an example that prints all built-in and custom document properties for an Office document to the Immediate window, see the PrintAllDocProperties procedure in the modDocumentPropertiesCode module in the ExcelExamples.xls file in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.

Note   The BuiltInDocumentProperties property returns a collection that contains properties that may apply only to certain Office applications. If you try to return the value of these properties in the wrong context, an error occurs. The sample code shows how to trap this error and continue to identify all the properties that are valid in a given context.

The following code sample shows how to determine the value of a built-in document property. The GetBuiltInProperty procedure accepts an Office document object (Workbook, Document, or Presentation) and a property name and returns the value of the built-in property, if available:

Function GetBuiltInProperty(objDoc As Object, _
                            strPropname As String) As Variant

   ' This procedure returns the value of the built-in document
   ' property specified in the strPropName argument for the Office
   ' document object specified in the objDoc argument.

   Dim prpDocProp       As DocumentProperty
   Dim varValue         As Variant
   
   Const ERR_BADPROPERTY      As Long = 5
   Const ERR_BADDOCOBJ        As Long = 438
   Const ERR_BADCONTEXT       As Long = -2147467259
   
   On Error GoTo GetBuiltInProp_Err
   
   Set prpDocProp = objDoc.BuiltInDocumentProperties(strPropname)
   With prpDocProp
      varValue = .Value
      If Len(varValue) <> 0 Then
         GetBuiltInProperty = varValue
      Else
         GetBuiltInProperty = "Property does not currently have a value set."
      End If
   End With
   
GetBuiltInProp_End:
   Exit Function
GetBuiltInProp_Err:
   Select Case Err.Number
      Case ERR_BADDOCOBJ
         GetBuiltInProperty = "Object does not support BuiltInProperties."
      Case ERR_BADPROPERTY
         GetBuiltInProperty = "Property not in collection."
      Case ERR_BADCONTEXT
         GetBuiltInProperty = "Value not available in this context."
      Case Else
   End Select
   Resume GetBuiltInProp_End:
End Function

The GetBuiltInProperty procedure is available in the modDocumentPropertiesCode module in ExcelExamples.xls in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.

Note   For a complete list of built-in document properties, search the Microsoft Office Visual Basic Reference Help index for "DocumentProperty object."

You can determine the value of an existing custom document property by using the same techniques as those illustrated in the previous code example. The only difference is that you would use the Office document's CustomDocumentProperties collection to return the DocumentProperty object you were interested in.

You use the Add method of the CustomDocumentProperties collection to add a custom DocumentProperty object to the DocumentProperties collection. When you add a custom property, you specify its name, data type, and value. You can also link a custom property to a value in the Office document itself. When you add linked properties, the value of the custom property changes when the value in the document changes. For example, if you add a custom property linked to a named range in an Excel spreadsheet, the property will always contain the current value of the data in the named range.

The following procedure illustrates how to add both static and linked custom properties to the DocumentProperties collection. It is essentially a wrapper around the Add method of the DocumentProperties collection that includes parameter validation and deletes any existing custom property before adding a property that uses the same name.

Function AddCustomDocumentProperty(strPropName As String, _
                                 lngPropType As Long, _
                                 Optional varPropValue As Variant = "", _
                                 Optional blnLinkToContent As Boolean = False, _
                                 Optional varLinkSource As Variant = "") _
                                 As Long
                         
   ' This procedure adds the custom property specified in the strPropName
   ' argument. If the blnLinkToContent argument is True, the custom
   ' property is linked to the location specified by varLinkSource.
   ' The procedure first checks for missing or inconsistent input parameters.
   ' For example, a value must be provided unless the property is linked, and
   ' when you are using linked properties, the source of the link must be provided.

   Dim prpDocProp As DocumentProperty
   
   ' Validate data supplied in arguments to this procedure.
   If blnLinkToContent = False And Len(varPropValue) = 0 Then
      ' No value supplied for custom property.
      AddCustomDocumentProperty = ERR_CUSTOM_LINKTOCONTENT_VALUE
      Exit Function
   ElseIf blnLinkToContent = True And Len(varLinkSource) = 0 Then
      ' No source provided for LinkToContent scenario.
      AddCustomDocumentProperty = ERR_CUSTOM_LINKTOCONTENT_LINKSOURCE
      Exit Function
   ElseIf lngPropType < msoPropertyTypeNumber Or _
         lngPropType > msoPropertyTypeFloat Then
      ' Invalid value for data type specifier. Must be one of the
      ' msoDocProperties enumerated constants.
      AddCustomDocumentProperty = ERR_CUSTOM_INVALID_DATATYPE
      Exit Function
   ElseIf Len(strPropName) = 0 Then
      ' No name supplied for new custom property.
      AddCustomDocumentProperty = ERR_CUSTOM_INVALID_PROPNAME
      Exit Function
   End If
   
   Call DeleteIfExisting(strPropName)

   Select Case blnLinkToContent
      Case True
         Set prpDocProp = ActiveWorkbook.CustomDocumentProperties _
            .Add(Name:=strPropName, LinkToContent:=blnLinkToContent, _
            Type:=lngPropType, LinkSource:=varLinkSource)
            ActiveWorkbook.Save
      Case False
         Set prpDocProp = ActiveWorkbook.CustomDocumentProperties. _
            Add(Name:=strPropName, LinkToContent:=blnLinkToContent, _
            Type:=lngPropType, Value:=varPropValue)
   End Select
End Function

The AddCustomDocumentProperty procedure is available in the modDocumentPropertiesCode module in ExcelExamples.xls in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.

Important   When you programmatically add a custom property to the DocumentProperties collection and the property is linked to a value in the underlying Office document, you must use the document's Save method, as illustrated above, before the property value will be correctly reflected for the new DocumentProperty object.