Document Properties

This page describes how to use the Built-In and Custom document properties of a workbook.

Introduction

All Office applications (Excel, Word, PowerPoint, etc.) support document properties, named values that provide information about the document, such as the date and time at which the document was last saved, the last user to modify the document, and so on. Document properties are either built into the document, or are custom, user defined properties. Because the document properties are shared by all Office applications, not all built-in properties are supported by all Office programs. For example, the Last Save Time applies to all applications, so it is supported by all applications. However, the Number Of Slides property applies only to PowerPoint, and thus is not supported in other applications, such as Excel or Word. Some properties may seem like they are relevant to an application, but that the application may not maintain the property. For example, the Total Editing Time property seems like it would be relevant to all Office applications, but is, in fact, supported only by Word. I use the phrase "not maintained" to mean that the application does not update those properties when a file is closed. However, those unused properties still exist in the workbook, so you can use them rather than using a Custom property if you so desire. (There is no advantage to using a BuiltIn property rather than a Custom property.) For example, it is perfectly legal in Excel to assign a value to the Number Of Slides property. Excel won't do anything with that property, but it can be used to store data.

You can read and manually add or modify some BuiltIn properties and all Custom properties by selecting Properties from the File menu (Excel 2003 and earlier) or from the Prepare item on the Office command button menu in Excel 2007 and later. This page contains code for automating the BuiltIn and Custom properties.

This page describes VBA code that you can use to read and write values to both BuiltIn and Custom document properties. There are two primary functions described on this page, GetProperty and SetProperty. The names of these function make clear their purposes: GetProperty returns the value of a property from either the BuiltIn or Custom property set, and SetProperty changes the value of a BuiltIn property or changes (and creates if need be) a Custom property.

The code for the functions is quite long, as it provides great flexibility. Due to the length of the procedures, the full listing will not be included on this page. You can download a zip file that contains all the code (and support functions) in a bas file that you can import directly in to your project.

NOTE: The Intellisense prompts in VBA show that the Type parameter is optional when setting a custom document property. This is incorrect -- the Type parameter is required and should be set to a member of the Office.MsoDocProperties enumeration, typically msoPropertyTypeString or msoPropertyTypeNumber.

The PropertySet Parameter

These function allow you to specify whether to look in the BuiltIn properties set or the Custom properties set, or both. This is what the PropertySet parameter to the function is used for. This parameter may have one of the following values:

    Public Enum PropertyLocation
        PropertyLocationBuiltIn = 1
        PropertyLocationCustom = 2
        PropertyLocationBoth = 3
    End Enum

You would specify PropertyLocationBuiltIn to look only at BuiltIn properties, PropertyLocationCustom to look only at the Custom properties, or PropertyLocationBoth to look at both the BuiltIn and Custom property sets.

The GetProperty Function

The GetProperty function returns a property value from a workbook's BuiltIn or Custom property sets. The procedure declaration is shown below:

    Function GetProperty(PropertyName As String, PropertySet As PropertyLocation, _
        Optional WhatWorkbook As Workbook) As Variant

In this code, PropertyName is the name of the property whose value should be returned. PropertySet is a value, described above, to specify the BuiltIn property set, the Custom property set, or both. WhatWorkbook specifies the workbook whose properties are to be examined. If this is missing or Nothing, ThisWorkbook is used. The function will be the value of the property, if it exists, or Empty if the property exist but has no value in it, or NULL if the property does not exist. When you use this function, be sure to use the IsNull and IsEmpty functions to fully test the result of the function.

The SetProperty Function

The SetProperty function is used to update the value of a BuiltIn property or a Custom property. If necessary, it will create a new Custom property. The procedure declaration is shown below:

    Function SetProperty(PropertyName As String, PropertySet As PropertyLocation, _
        PropertyValue As Variant, Optional ContentLink As Boolean = False, _
        Optional WhatWorkbook As Workbook) As Boolean

In This code, PropertyName is the name of the property to update (or create). PropertySet is a value described above to indicate whether to add or update the property in the BuiltIn or Custom property set. The PropertyVlaue parameter has dual roles, depending on the value of ContentLink. If ContentLink is False, PropertyValue is the value used to update PropertyName. If ContentLink is TRUE, PropertyValue may be either a String containing the Name of a range that the property will be linked to, or it may be a Name Object to which the property will be linked. If ContentLink is TRUE, and if PropertyValue is anything other than the name of an existing Name or a Name object, an error will occur and the function will return FALSE. ContentLink is either TRUE or FALSE indicating whether the property (a Custom property only -- BuiltIn properties can't link) is linked to a named range. If this is TRUE, PropertyValue must be a String containing the name of an existing Name object or a Name object itself. If ContentLink is omitted, it is assumed to be FALSE and no linking is done. WhatWorkbook specifies the workbook whose properties will be updated. If this parameter is omitted or is Nothing, ThisWorkbook is used.

The WritePropertiesToRange Function

The WritePropertiesToRange function write out property names and their values to a 2-column range on a worksheet. The function declaration is shown below:

    Function WritePropertiesToRange(PropertySet As PropertyLocation, FirstCell As Range, _
        Optional WhatWorkbook As Workbook) As Long

In this code, PropertySet is a value, described above, to indicate that the BuiltIn properties, the Custom properties, or both sets are to be listed. FirstCell refers to the cell on which the listing is to begin. WhatWorkbook specifies the workbook whose properties are to be listed. If this is omitted or Nothing, ThisWorkbook is used.

Returning Property Values From A Closed File

VBA doesn't directly support reading properties from closed files. However, Microsoft has made available for free download a DLL file called DSO OLE Document Properties Reader 2.1, or simply DSOFile.dll. With this DLL, you can read both BuiltIn and Custom properties from a OLE file, such as an XLS workbook. You can download DSOFile support.microsoft.com/kb/224351/en-us. Once you have downloaded an installed that file, you will need to go to the Tools menu in the VBA editor, choose References and scroll down to and check DSO OLE Document Properties Reader 2.1. Make sure you are using version 2.1, not the earlier and now obsolete version 1.4.

NOTE: See this MSDN blog item about using DSO with 64-bit Office and Windows, and how to get upgrade fixes.

The downloadable a zip file contains a function named ReadPropertyFromClosedFile which will read properties from a closed Excel file. The function declaration is shown below.

    Function ReadPropertyFromClosedFile(FileName As String, PropertyName As String, _
        PropertySet As PropertyLocation) As Variant

In this code, FileName is the fully-qualified file name whose properties you want to examine, PropertyName is the name of the BuiltIn or Custom property whose value you want to return, and PropertyLocation is described above, and specifies whether to look in BuiltIn properties, the Custom properties, or both. The function will return the value of the property if found, or NULL if the property was not found or an invalid parameter was passed to the function. Be sure to check the return value with the IsNull function.

Changing Or Adding A Property Of A Closed File

The DSOFile.dll not only allow you to read values from a close file, but also to add new custom properties or change the values of existing BuiltIn and Custom properties. The downloadable a zip file contains a function named WritePropertyToClosedFile that will write a value to a closed file. The function declaration is shown below:

    Function WritePropertyToClosedFile(FileName As String, PropertyName As String, _
        PropertyValue As String, PropertySet As PropertyLocation) As Boolean

In this code, FileName is the file whose properties you want to modify. PropertyName is the name of the property whose value you want to modify. PropertyValue is the new value for PropertyName. PropertySet is as discussed above and indicates whether to modify a value in the BuiltIn property set or the Custom property set.

You can download a zip file that contains all the code (and support functions) in a bas file that you can import directly in to your project.

This page last updated: 12-February-2013