Document Properties 

 

This Page Has Been Replaced. Click Here For The New Page.

Like all Microsoft Office documents (Word documents, PowerPoint presentations, etc), Excel workbooks support both Built-In and Custom document properties.   Built-In properties are those that are provided by the application itself, and Custom properties are those that you create.  Once you have created these functions in a VBA code module, you can call the directly from worksheet cells, with a formula like  =GetProperty("Author").

Built-In Document Properties

All Office documents share the same collection of Built-In Properties, which are accessed through the Workbook object.

ThisWorkbook.BuiltinDocumentProperties("Last Save Time").Value

Even those properties that do not make sense for Excel, such as "Number Of Slides", exist and may be assigned a value. Unfortunately, Excel does not maintain (i.e., assign and update the value) many of  the Built-In properties, including those, such as "Last Save Time" which are definitely relevant for Excel.   In general, it is best to maintain yourself the properties that you are interested in. 

If you attempt that read the value of a property that has not been assigned a value, you will receive an error. Therefore, you should include the proper error-handling code in your procedures if you are working with document properties. 

On Error Resume Next
Err.Clear
MsgBox ThisWorkbook.BuiltinDocumentProperties("Total Editing Time")
If Err.Number <> 0 Then
    MsgBox "Total Editing Time property not assigned."
End If

To assign a value to a Built-In property, you just assign a value to its Value property:

ThisWorkbook.BuiltinDocumentProperties("Last Save Time").Value = Now

The logical place to update Built-In document properties is in the Workbook code modules, or in an Application Event code module, so that your code will automatically execute as required.

Custom Document Properties

You can create your own document properties by adding an object to the CustomDocumentProperties collection object. When adding a new property, you cannot access the CustomDocumentProperties collection directly.  Instead, you must create an object of the DocumentProperties type, and set it the the CustomDocumentProperties collection for the workbook.

Dim DocProps As DocumentProperties
Set DocProps = ThisWorkbook.CustomDocumentProperties
DocProps.Add Name:="ChipTest", _
    LinkToContent:=False, _
    Type:=msoPropertyTypeNumber, _
    Value:=1234

Once you've added a member to the collection, you can access its value, either to read it or update it, directly through the CustomDocumentProperties collection.

MsgBox ThisWorkbook.CustomDocumentProperties("ChipTest").Value
ThisWorkbook.CustomDocumentProperties("ChipTest").Value = 4321

VBA Code For Working With Document Properties

This section includes two VBA procedures for working with document properties for an Excel workbook.  The first procedure, GetProperty, is a function which returns the value of a specified document property.  The second procedure, SetProperty, sets the value of either a BuiltIn or Custom Property.

GetProperty

This function returns the value of a property.  If there is a Builtin Property with the specified name, its value is returned.  If not, the Custom Property value is returned.  If there is neither a Builtin nor a Custom Property with the specified name, and empty string is returned.  It is not added.  The parameters passed to the function are as follows:

P                                   A string containing the name of the property

WorkbookName       A string containing the name of the workbook whose property is to be retrieved. 
                                     If missing, and the function is called from a worksheet cell, the workbook containing the
                                    cell is used.  If missing, and the workbook is called from VBA, the ActiveWorkbook is used.

Public Function GetProperty(P As String, Optional WorkbookName As Variant)
Dim S As Variant
Dim WB As Workbook
On Error Resume Next
If IsMissing(WorkbookName) Then
    If TypeOf Application.Caller Is Range Then
       Set WB = Application.Caller.Parent.Parent
    Else
       Set WB = ActiveWorkbook
    End If
Else
    Set WB = Workbooks(WorkbookName)
End If
S = WB.CustomDocumentProperties(P)
If S <> "" Then
    GetProperty = S
    Exit Function
End If
On Error GoTo EndMacro
GetProperty = WB.BuiltinDocumentProperties(P)
Exit Function
EndMacro:
GetProperty = ""
End Function
 

This function can be called from a worksheet cell with a formula like
=GETPROPERTY("Last Save Time")

 

SetProperty

This macro sets the value of a property.  The property is added if it does not exist.  The parameters passed to the function are as follows:

WorkbookName       A string containing the name of the workbook whose property is to be set.  If missing, the ActiveWorkbook is used.
PName                         A string containing the name of the property
PValue                      A variant containing the value of the property
PropCustom            A boolean indicating whether the property is a Custom Document Property.

Sub SetProperty(WorkbookName As String, PName As String, _
    PValue As Variant, PropCustom As Boolean)

Dim DocProps As DocumentProperties
Dim TheType As Long

On Error Resume Next
If PropCustom = True Then
    Set DocProps = Workbooks(WorkbookName).CustomDocumentProperties
Else
    Set DocProps = Workbooks(WorkbookName).BuiltinDocumentProperties
End If
Select Case VarType(PValue)
    Case vbBoolean
        TheType = msoPropertyTypeBoolean
    Case vbDate
        TheType = msoPropertyTypeDate
    Case vbDouble, vbLong, vbSingle, vbCurrency
        TheType = msoPropertyTypeFloat
    Case vbInteger
        TheType = msoPropertyTypeNumber
    Case vbString
        TheType = msoPropertyTypeString
    Case Else
        TheType = msoPropertyTypeString
End Select

DocProps.Add Name:=PName, LinkToContent:=False, Type:=TheType, Value:=PValue
DocProps(PName).Value = PValue

End Sub

Document Properties Of Closed Files

The code above works for reading properties in files that Excel has open.  They won't work for reading properties of closed files.  There is a library of procedures available that allows you to read (and, in some cases, write) properties of closed files.  Moreover, you can read properties of any OLE Structured Storage file, such as Word documents, PowerPoint presentations, and so on.  Your code will need a reference to the "DS: OLE Document Properties 1.2 Object Library" .  Go to the Tools menu, choose References, and select this library.  If you do not have this library installed, you can download it for free from Microsoft at  http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224351 .

NOTE: The code below works for DSO version 1.2 only. It will not work with version 2.0.  See below for example code for 2.0.

Once you've established this reference, you can the following code to retrieve properties from a closed file.

Dim FileName As String
Dim DSO As DSOleFile.PropertyReader
Set DSO = New DSOleFile.PropertyReader
FileName = "C:\Book1.xls"
With DSO.GetDocumentProperties(sfilename:=FileName)
    Debug.Print .AppName
    Debug.Print .Author
    Debug.Print .ByteCount
    Debug.Print .Company
    '
    ' lots more properties
    '
End With

The code above works for the standard built-in document propreties. To access custom document properties, you must go through the CustomProperties collection. For example,

    Debug.Print .CustomProperties("Cust Prop").Value


If you attempt to read the properties of a workbook is open, you'll get an error.  If you want to read the properties of an open file, such as the file containing the code, you can change to the file access to Read Only, read the properties, and then restore the access to Read-Write.  For example,

Dim FileName As String
Dim DSO As DSOleFile.PropertyReader
Set DSO = New DSOleFile.PropertyReader
ThisWorkbook.ChangeFileAccess xlReadOnly
FileName = ThisWorkbook.FullName
With DSO.GetDocumentProperties(sfilename:=FileName)
    Debug.Print .AppName
    Debug.Print .Author
    Debug.Print .ByteCount
    Debug.Print .Company
    '
    ' lots more properties
    '
End With
ThisWorkbook.ChangeFileAccess xlReadWrite

 

Document Properties Of Closed Files DSO Version 2.0

The following code illustrates how to read document properties using DSO version 2.0. You can download DSO Version 2.0 from Microsoft at 

http://www.microsoft.com/downloads/details.aspx?FamilyID=9ba6fac6-520b-4a0a-878a-53ec8300c4c2&DisplayLang=en

Dim FileName As String
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties
FileName = "C:\Book1.xls"
DSO.Open sfilename:=FileName
Debug.Print DSO.SummaryProperties.ApplicationName
Debug.Print DSO.SummaryProperties.Author
' lots of other properties
DSO.Close