G
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.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
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
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
K
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
it in a calculation. I'm working with Excel 2000 in a
Windows 2000 LAN.
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
in a worksheet. Just to show this date, but also to use-----Original Message-----
I like to have the Date Last Saved displayed in a cell
it in a calculation. I'm working with Excel 2000 in a
Windows 2000 LAN.
B
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)
with Excel 2000 in a Windows 2000 LAN.
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)
Just to show this date, but also to use it in a calculation. I'm workingDerk said:I like to have the Date Last Saved displayed in a cell in a worksheet.
with Excel 2000 in a Windows 2000 LAN.