AboutDouglas M. Smith Expertise I specialize in solving formula, feature and structure related problems. I know many tricks to help make your spreadsheets and processing more efficient.
Experience Brainbench MVP for MS Excel
Past/Present clients Gannett, Fannie Mae, Pepsi, Nortel, Procter & Gamble, BellSouth, Blue Cross Blue Shield of NC, NC Central University, GlaxoSmithKline, Maintenance Excellence Institute, AAI Pharmaceuticals, Blue Cross Blue Shield of SC, Brainbench.com
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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
