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

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
 

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 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