Date Last Saved

[Guest]

I like to have the Date Last Saved displayed in a cell in a worksheet. Just to show this date, but also to use it in a calculation. I'm working with Excel 2000 in a Windows 2000 LAN.

 

[Frank Kabel]

Hi Derk
you may use the following UDF (put it in a module of your workbook):
Public Function last_created(filename As String) As Date
Dim oFS As New Scripting.FileSystemObject
Dim oFL As Scripting.File
Set oFL = oFS.GetFile(filename)
last_created = oFL.DateLastModified
End Function

e.g.
=last_created("C:\temp\book_test.xls")
will return the last saved date

Frank

 

[Kathy]

I found the following code in a newsgroup a couple of
weeks ago. You will need to create a "range" my was
entilted "update" and refernce the range in the code. If
you need to use this cell A1 in a formula then I would
try A2=A1 then put A2 in all formulas. But you may want
to test that -

Make sure no data is in cell A1 or no cell references in
formulas refer to cell A1. - Formula references are for
A2.

Highlight cell A1
Select your Insert drop down menu
Select the Name option
Within this window, Choose Define Name option, then NAME
cell A1"update",

Select the Window Restore down option at upper right hand
of screen. (reduces size of window to show blue bar at
top of workbook window) Highlight blue bar of active
spreadsheet window. Right click mouse, choose View Code
option.
That will open the Code Window entitled "Microsoft Visual
Basic" window.

Copy and paste this code in the Microsoft Visual Basic
window.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
Range("update").Value = Date
End Sub


Next, Close window
Save and close file
Close Excel

Reopen Excel and file
edit any cell but A1
save file - then reopen - date should be displayed in
cell A1.

Next reference all forumla to A2 were A2=A1

Good Luck
kathy

-----Original Message-----
I like to have the Date Last Saved displayed in a cell

in a worksheet. Just to show this date, but also to use
it in a calculation. I'm working with Excel 2000 in a
Windows 2000 LAN.

 

[Bob Phillips]

You need VBA, but you could use a UDF. Here is an example

Function DocProps(prop As String)

On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties(prop)
Exit Function

err_value:
DocProps = CVErr(xlErrValue)
End Function



and can be used like so

=DocProps("Last save time")

and format to suit

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I like to have the Date Last Saved displayed in a cell in a worksheet.

Just to show this date, but also to use it in a calculation. I'm working
with Excel 2000 in a Windows 2000 LAN.