show date file updated?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Hughes - 07 Jan 2008 16:49 GMT

Can I have a field that automatically shows the date when the file was last
updated?

Reply to this Message

Chip Pearson - 07 Jan 2008 17:11 GMT

You can use code like

Sub LastSaveTime()
With ThisWorkbook
If .FullName <> vbNullString Then
.Worksheets("Sheet1").Range("A1").Value = _
FileDateTime(.FullName)
End If
End With
End Sub

This will put the last saved date and time into cell A1 on Sheet1.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Can I have a field that automatically shows the date when the file was
> last
> updated?

Reply to this Message

Chip Pearson - 07 Jan 2008 17:23 GMT

If you want a function that can be called from a worksheet cell, use

Function LastSaveTime(R As Range) As Date
Application.Volatile True
With R.Worksheet.Parent
If .FullName <> vbNullString Then
LastSaveTime = CDate(FileDateTime(.FullName))
End If
End With
End Function

Then call this from a cell with the formula

=LastSaveTime(A1)

The cell passed to the function doesn't matter. It is used to select the
workbook whose last save time is to be returned.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> You can use code like
>
[quoted text clipped - 12 lines]
>> last
>> updated?

Reply to this Message

Dave Peterson - 07 Jan 2008 17:41 GMT

I bet you meant:

If .Path <> vbNullString Then

instead of checking .fullname.

> If you want a function that can be called from a worksheet cell, use
>
[quoted text clipped - 46 lines]
> >> last
> >> updated?

Signature

Dave Peterson

Reply to this Message

Chris Hughes - 08 Jan 2008 14:21 GMT

Thanks for that.

Sorry, I'm a code newbie -- how do you put the code into excel to make it a
function?

Reply to this Message

Dave Peterson - 08 Jan 2008 14:56 GMT

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Then read Chip Pearson's site:
http://cpearson.com/excel/WritingFunctionsInVBA.aspx

> Thanks for that.
>
> Sorry, I'm a code newbie -- how do you put the code into excel to make it a
> function?

Signature

Dave Peterson

Reply to this Message

Bob Phillips - 07 Jan 2008 17:16 GMT

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(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")

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Can I have a field that automatically shows the date when the file was
> last
> updated?

Reply to this Message