Workbook Properties

[jesterhs]

Hello Everyone. I am hoping there is a way to accomplish the following. I would like to be able to pull the Creation Date and Last Modified Date from the workbook proprties. For example, when you go to File>Properties, you see the properties box and under the general tab it shows the creation, last modified, and last accessed dates. Please tell me there is a way to put those in a worksheet.

Thanks in advance!

[dominicb]

Good evening Jesterhs

You can't do it through a formula unless you write your own function to do so : the code below will give you a basis to start from and enable you to do it through VBA or build a function to do it for you.

Sub Properties()
a = ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value
MsgBox a
End Sub

The words "Creation Date" can be replaced by any of the values below to return the appropriate workbook properties:

Title
Subject
Author
Keywords
Comments
Template
Last Author
Revision Number
Application Name
Last Print 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)

Obviously, just because there is a property available, doesn't mean that there is automatically a value assigned to it.

HTH

DominicB

[jesterhs]

Thanks DominicB. This works great. I have one last question for you. I created 2 functions, Creation Date and Last Save Time, and I was wondering if there is a way to trigger those cells to update anytime the workbook is saved. If you any thoughts that you think would be better I am all ear... or...eyes!

Thanks,

Jesterhs

[dominicb]

Hi Jesterhs

Thanks for the feedback - always appreciated.

If you created a function that is called through the worksheet from a formula (eg, if you called it =lastsavedtime()) then you would find that this would be recalculated every time ANY cell contents changed. However you could force a recalc befor save by using this macro:

Sub auto_close()
Calculate
End Sub

However, if you are calling it from a VBA routine you just need to run the macro code from a module called

Sub auto_open
to open it from start up or

Sub auto_close
to run it upon close

If you are feeling adventurous you could put the code into an event procedure in the workbook module:

Sub Workbook_Open
or
Sub Workbook_BeforeClose

For more information about this see Chip Pearson's site for some detail.
http://www.cpearson.com/excel/events.htm

Good luck

HTH

DominicB