Excel should have a function for last date and time file was saved

[Guest]

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Bob Phillips]

Why wait?

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocument­Properties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=891496a8-5e
51-46a4-b6dd-4daa6be92808&dg=microsoft.public.excel.worksheet.functions

 

[Guest]

I can't seem to get this to work with Excel 2003. Is there anything special
I need to do? Or is it already in there somewhere?

 

[Gord Dibben]

Dark T

First off.........save a backup of your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste Bob's Function DocProps code in there.

Save the workbook and hit ALT + Q to return to Excel window.

Enter the formula in any cell Bob has shown in his post.

For a list of other properties that may be available(not all are), run this
macro.

Sub props()
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
rw = rw + 1
Next
End Sub


Gord Dibben Excel MVP

 

[Guest]

I have done that. I input the function:

=DocProps("Last save time")

and it gives me this error:

#VALUE!

What does that mean? Any idea?

Dark Thunder

PS. Thanks for the list of other properties!

 

[Gord Dibben]

It could mean you have not yet saved the workbook.

Are you doing this in a new workbook?


Gord

 

[Guest]

No... I have saved this workbook several times.

DT

 

[Guest]

Nevermind... I found my problem. There was hyphen added to the function code
when I copied it over. It works now. Thanks for your help!

DT

 

[Gord Dibben]

Thanks for the feedback.

We got hit months ago by a rash of those extra hyphens being inserted in a lot
of posts.

Haven't seen it lately.

Hope this is not a precursor to another epidemic.


Gord

 

[Eric]

How do I get this to work with 2007? or is there a better way to
insert save date? I get error #NAME?

 

[Guest]

I could not get the following to work. Also how would I get the information
of another file in the active workbook?

Sub props()
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
rw = rw + 1
Next
End Sub
_____________________________________________
Function DocProps(props As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocument.Properties _
(props)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

 

[Gord Dibben]

What is "not work"?

The Sub props() just places a list of the built-in document properties available
in Excel on a new worksheet.

The DocProps function is used in a cell to return the actual property.

=DocProps("last save time") will return that value.

Note: some of the document properties are not returned by this function.

To see which ones are returned run the macro first then in B1 enter

=DocProps(A1) and copy down.


Gord