Excel/Excel - How to access Workbook Properties
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.
Microsoft Excel 2003 running on Windows XP
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
Returns a DocumentProperties collection that represents all the built-in document properties for the specified workbook. Read-only.
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:
Last Print Date
Last Save Time
Total Editing Time
Number of Pages
Number of Words
Number of Characters
Number of Bytes
Number of Lines
Number of Paragraphs
Number of Slides
Number of Notes
Number of Hidden Slides
Number of Multimedia Clips
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:
Use the CustomDocumentProperties property to return the collection of custom document properties.
This example displays the names of the built-in document properties as a list on worksheet one.
rw = 1
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
rw = rw + 1