February 07, 2009

Document Properties

Document properties allow us to get information about a workbook. Default properties are called BuiltinDocumentProperties in VBA. Straight from the Help files, here is some slightly modified code to view these properties and their values.

Sub ShowBuiltinDocumentProperties()
Dim rw As Long
Dim p As DocumentProperty

On Error Resume Next

For Each p In ActiveWorkbook.BuiltinDocumentProperties
    
    Cells(rw + 1, 1).Value = p.Name
    Cells(rw + 1, 2).Value = p.Value
    
    rw = rw + 1

Next p

On Error GoTo 0
End Sub


And here they are in Excel 2007. I'm not sure whether they are the same in all Excel versions, I'll leave that with you to find out.

Title
Subject
Author
Keywords
Comments
Template
Last author
Revision number
Application name
Last print date
Creation date
Last save time
Total editing time
Number of pages
Number of words
Number of characters
Security
Category
Format
Manager
Company
Number of bytes
Number of lines
Number of paragraphs
Number of slides
Number of notes
Number of hidden Slides
Number of multimedia clips
Hyperlink base
Number of characters (with spaces)
Content type
Content status
Language
Document version

To edit a value, use this code, changing the property name and value where required.
ThisWorkbook.BuiltinDocumentProperties("Author").Value = "Andrew Engwirda"

Note that you will need to use the right type of value, for example, you cannot use text where Excel would expect a number.

Short and sweet, next time I'll look at CustomDocumentProperties and what you can do with them.  

Posted by andrewe at 11:42