Expert: Douglas M. Smith - 9/10/2004

Question
Hi Doug,

I have a client who asked me HOW to access the info kept in the properties sheet of an Excel Workbook.  I know that the Excel object model probably exposes these properties via VBA but this end user does not have that type of expertise.  Is there a simpler way to access the property sheet using an informational type function?
What he is basically trying to do is time stamp and possibly user stamp a spreadsheet.  Since this is done automatically by Excel anyway, he was asking me for advice on how to access these properties and/or if I knew a workaround for doing this without using VBA.  The key is that he does not want the time stamp to change (ie can't use Today() or Now() functions in a live state) and he doesn't want the user to have to press a button or remember to enter this info.

Any thoughts?

Joe
Microsoft Excel 2003 running on Windows XP

Answer
Hi Joe,

This information is only accessible through VBA.  I've included the VBA help text for BuiltinDocumentProperties which is how these properties are accessed.  There is a short macro at the end that reads the available values and writes them to a worksheet.  This might solve your problem.

There are ways to put timestamps in cells but they too will require a macro.  For example, a macro could be set up to monitor certain user activities, such as entering data, and then automatically place the timestamp in a predesignated location when certain cell values are changed.

Douglas M. Smith
Brainbench MVP for MS Excel
www.brainbench.com
www.abundant-solutions.com

=======================================================
BuiltinDocumentProperties Property
Returns a DocumentProperties collection that represents all the built-in document properties for the specified workbook. Read-only.

Remarks
This property returns the entire collection of built-in document properties. Use the Item method to return a single member of the collection (a DocumentProperty object) by specifying either the name of the property or the collection index (as a number).

You can refer to document properties either by index value or by name. The following list shows the available built-in document property names:

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)



Container applications aren't required to define values for every built-in document property. If Microsoft Excel doesn't define a value for one of the built-in document properties, reading the Value property for that document property causes an error.

Because the Item method is the default method for the DocumentProperties collection, the following statements are identical:

BuiltinDocumentProperties.Item(1)
BuiltinDocumentProperties(1)
Use the CustomDocumentProperties property to return the collection of custom document properties.

Example
This example displays the names of the built-in document properties as a list on worksheet one.

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