Last Saved Date and Time

Kim · 08-12-2005, 05:05 AM

I created a function in Excel to display the "Last Saved Date and Time" in a
cell. The code is as follows:

<
Function LastSaved( )
LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
End Function
>

After saving the modified file, the cell displays the "Last Saved Date and
Time". I tried to modify the file another time and saved it. The cell however
does not change to the new date and time. But this was reflected under the
File -> Properties -> Statistics.

How can I update the cell to continuous display the right date and time
whenever the "Save" button is clicked ?

Bob Phillips · 08-12-2005, 05:05 AM

This is because saving a file does not trigger a worksheet calculation, and
it doesn't update when there is some calculation triggered by other changes,
so your function doesn't update. You can force a recalc with Ctrl-Calc-F9,
or you can add Application.Volatile to the function so that it gets
recalculated on an F9 or when some other change occurs that does trigger a
recalc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Kim" <[email protected]> wrote in message
news:[email protected]...
> I created a function in Excel to display the "Last Saved Date and Time" in
a
> cell. The code is as follows:
>
> <
> Function LastSaved( )
> LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> End Function
> >
>
> After saving the modified file, the cell displays the "Last Saved Date and
> Time". I tried to modify the file another time and saved it. The cell
however
> does not change to the new date and time. But this was reflected under the
> File -> Properties -> Statistics.
>
> How can I update the cell to continuous display the right date and time
> whenever the "Save" button is clicked ?

Andibevan · 08-12-2005, 06:06 AM

Could you not also put calculate in the beforesave event? If you ordered it
correctly you could actually get it to calculate after it had saved the
file?

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> This is because saving a file does not trigger a worksheet calculation,
and
> it doesn't update when there is some calculation triggered by other
changes,
> so your function doesn't update. You can force a recalc with Ctrl-Calc-F9,
> or you can add Application.Volatile to the function so that it gets
> recalculated on an F9 or when some other change occurs that does trigger a
> recalc.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Kim" <[email protected]> wrote in message
> news:[email protected]...
> > I created a function in Excel to display the "Last Saved Date and Time"
in
> a
> > cell. The code is as follows:
> >
> > <
> > Function LastSaved( )
> > LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> > End Function
> > >
> >
> > After saving the modified file, the cell displays the "Last Saved Date
and
> > Time". I tried to modify the file another time and saved it. The cell
> however
> > does not change to the new date and time. But this was reflected under
the
> > File -> Properties -> Statistics.
> >
> > How can I update the cell to continuous display the right date and time
> > whenever the "Save" button is clicked ?
>
>

Bob Phillips · 08-12-2005, 07:05 AM

Good thought Andi, that should work.

Kim,

This is the code that Andi refers to

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.Calculate
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> Could you not also put calculate in the beforesave event? If you ordered
it
> correctly you could actually get it to calculate after it had saved the
> file?
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > This is because saving a file does not trigger a worksheet calculation,
> and
> > it doesn't update when there is some calculation triggered by other
> changes,
> > so your function doesn't update. You can force a recalc with
Ctrl-Calc-F9,
> > or you can add Application.Volatile to the function so that it gets
> > recalculated on an F9 or when some other change occurs that does trigger
a
> > recalc.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Kim" <[email protected]> wrote in message
> > news:[email protected]...
> > > I created a function in Excel to display the "Last Saved Date and
Time"
> in
> > a
> > > cell. The code is as follows:
> > >
> > > <
> > > Function LastSaved( )
> > > LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> > > End Function
> > > >
> > >
> > > After saving the modified file, the cell displays the "Last Saved Date
> and
> > > Time". I tried to modify the file another time and saved it. The cell
> > however
> > > does not change to the new date and time. But this was reflected under
> the
> > > File -> Properties -> Statistics.
> > >
> > > How can I update the cell to continuous display the right date and
time
> > > whenever the "Save" button is clicked ?
> >
> >
>
>

Andibevan · 08-12-2005, 08:05 AM

Bob,

Would that not calculate it before it saves? Therefore it would show the
time of the previous save.

Would this work:-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
cancel = true

Application.Calculate
ActiveWorkbook.Save
End Sub

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Good thought Andi, that should work.
>
> Kim,
>
> This is the code that Andi refers to
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Application.Calculate
> End Sub
>
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Andibevan" <[email protected]> wrote in message
> news:[email protected]...
> > Could you not also put calculate in the beforesave event? If you
ordered
> it
> > correctly you could actually get it to calculate after it had saved the
> > file?
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is because saving a file does not trigger a worksheet
calculation,
> > and
> > > it doesn't update when there is some calculation triggered by other
> > changes,
> > > so your function doesn't update. You can force a recalc with
> Ctrl-Calc-F9,
> > > or you can add Application.Volatile to the function so that it gets
> > > recalculated on an F9 or when some other change occurs that does
trigger
> a
> > > recalc.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Kim" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I created a function in Excel to display the "Last Saved Date and
> Time"
> > in
> > > a
> > > > cell. The code is as follows:
> > > >
> > > > <
> > > > Function LastSaved( )
> > > > LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> > > > End Function
> > > > >
> > > >
> > > > After saving the modified file, the cell displays the "Last Saved
Date
> > and
> > > > Time". I tried to modify the file another time and saved it. The
cell
> > > however
> > > > does not change to the new date and time. But this was reflected
under
> > the
> > > > File -> Properties -> Statistics.
> > > >
> > > > How can I update the cell to continuous display the right date and
> time
> > > > whenever the "Save" button is clicked ?
> > >
> > >
> >
> >
>
>

Bob Phillips · 08-12-2005, 01:05 PM

Andi,

It's good someone is on the ball.

This should do it

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.OnTime Now + TimeSerial(0, 0, 1), "ReCalc"
End Sub

and add this proc to a standard module

Sub Recalc()
Application.CalculateFull
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Would that not calculate it before it saves? Therefore it would show the
> time of the previous save.
>
> Would this work:-
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> cancel = true
>
> Application.Calculate
> ActiveWorkbook.Save
> End Sub
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Good thought Andi, that should work.
> >
> > Kim,
> >
> > This is the code that Andi refers to
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > Application.Calculate
> > End Sub
> >
> >
> > 'This is workbook event code.
> > 'To input this code, right click on the Excel icon on the worksheet
> > '(or next to the File menu if you maximise your workbooks),
> > 'select View Code from the menu, and paste the code
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Could you not also put calculate in the beforesave event? If you
> ordered
> > it
> > > correctly you could actually get it to calculate after it had saved
the
> > > file?
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is because saving a file does not trigger a worksheet
> calculation,
> > > and
> > > > it doesn't update when there is some calculation triggered by other
> > > changes,
> > > > so your function doesn't update. You can force a recalc with
> > Ctrl-Calc-F9,
> > > > or you can add Application.Volatile to the function so that it gets
> > > > recalculated on an F9 or when some other change occurs that does
> trigger
> > a
> > > > recalc.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Kim" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I created a function in Excel to display the "Last Saved Date and
> > Time"
> > > in
> > > > a
> > > > > cell. The code is as follows:
> > > > >
> > > > > <
> > > > > Function LastSaved( )
> > > > > LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> > > > > End Function
> > > > > >
> > > > >
> > > > > After saving the modified file, the cell displays the "Last Saved
> Date
> > > and
> > > > > Time". I tried to modify the file another time and saved it. The
> cell
> > > > however
> > > > > does not change to the new date and time. But this was reflected
> under
> > > the
> > > > > File -> Properties -> Statistics.
> > > > >
> > > > > How can I update the cell to continuous display the right date and
> > time
> > > > > whenever the "Save" button is clicked ?
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Bob Phillips · 08-12-2005, 01:05 PM

Also Andi, this seems to work no better. It doesn't even seem to save it.
Disabling events helps but still leaves it after the event.

But this works, better than the Ontime way

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
Cancel = True
ActiveWorkbook.Save
Application.CalculateFull
wb_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Would that not calculate it before it saves? Therefore it would show the
> time of the previous save.
>
> Would this work:-
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> cancel = true
>
> Application.Calculate
> ActiveWorkbook.Save
> End Sub
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Good thought Andi, that should work.
> >
> > Kim,
> >
> > This is the code that Andi refers to
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > Application.Calculate
> > End Sub
> >
> >
> > 'This is workbook event code.
> > 'To input this code, right click on the Excel icon on the worksheet
> > '(or next to the File menu if you maximise your workbooks),
> > 'select View Code from the menu, and paste the code
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Could you not also put calculate in the beforesave event? If you
> ordered
> > it
> > > correctly you could actually get it to calculate after it had saved
the
> > > file?
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is because saving a file does not trigger a worksheet
> calculation,
> > > and
> > > > it doesn't update when there is some calculation triggered by other
> > > changes,
> > > > so your function doesn't update. You can force a recalc with
> > Ctrl-Calc-F9,
> > > > or you can add Application.Volatile to the function so that it gets
> > > > recalculated on an F9 or when some other change occurs that does
> trigger
> > a
> > > > recalc.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Kim" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I created a function in Excel to display the "Last Saved Date and
> > Time"
> > > in
> > > > a
> > > > > cell. The code is as follows:
> > > > >
> > > > > <
> > > > > Function LastSaved( )
> > > > > LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> > > > > End Function
> > > > > >
> > > > >
> > > > > After saving the modified file, the cell displays the "Last Saved
> Date
> > > and
> > > > > Time". I tried to modify the file another time and saved it. The
> cell
> > > > however
> > > > > does not change to the new date and time. But this was reflected
> under
> > > the
> > > > > File -> Properties -> Statistics.
> > > > >
> > > > > How can I update the cell to continuous display the right date and
> > time
> > > > > whenever the "Save" button is clicked ?
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Kim · 08-14-2005, 10:05 PM

I've tried the last suggestion by Bob.
The date and time doesn't change when I saved the worksheet.

"Bob Phillips" wrote:

> Also Andi, this seems to work no better. It doesn't even seem to save it.
> Disabling events helps but still leaves it after the event.
>
> But this works, better than the Ontime way
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Application.EnableEvents = False
> Cancel = True
> ActiveWorkbook.Save
> Application.CalculateFull
> wb_exit:
> Application.EnableEvents = True
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Andibevan" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > Would that not calculate it before it saves? Therefore it would show the
> > time of the previous save.
> >
> > Would this work:-
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > cancel = true
> >
> > Application.Calculate
> > ActiveWorkbook.Save
> > End Sub
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > Good thought Andi, that should work.
> > >
> > > Kim,
> > >
> > > This is the code that Andi refers to
> > >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > > Application.Calculate
> > > End Sub
> > >
> > >
> > > 'This is workbook event code.
> > > 'To input this code, right click on the Excel icon on the worksheet
> > > '(or next to the File menu if you maximise your workbooks),
> > > 'select View Code from the menu, and paste the code
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Andibevan" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Could you not also put calculate in the beforesave event? If you
> > ordered
> > > it
> > > > correctly you could actually get it to calculate after it had saved
> the
> > > > file?
> > > >
> > > > "Bob Phillips" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > This is because saving a file does not trigger a worksheet
> > calculation,
> > > > and
> > > > > it doesn't update when there is some calculation triggered by other
> > > > changes,
> > > > > so your function doesn't update. You can force a recalc with
> > > Ctrl-Calc-F9,
> > > > > or you can add Application.Volatile to the function so that it gets
> > > > > recalculated on an F9 or when some other change occurs that does
> > trigger
> > > a
> > > > > recalc.
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Kim" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I created a function in Excel to display the "Last Saved Date and
> > > Time"
> > > > in
> > > > > a
> > > > > > cell. The code is as follows:
> > > > > >
> > > > > > <
> > > > > > Function LastSaved( )
> > > > > > LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> > > > > > End Function
> > > > > > >
> > > > > >
> > > > > > After saving the modified file, the cell displays the "Last Saved
> > Date
> > > > and
> > > > > > Time". I tried to modify the file another time and saved it. The
> > cell
> > > > > however
> > > > > > does not change to the new date and time. But this was reflected
> > under
> > > > the
> > > > > > File -> Properties -> Statistics.
> > > > > >
> > > > > > How can I update the cell to continuous display the right date and
> > > time
> > > > > > whenever the "Save" button is clicked ?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>

Bob Phillips · 08-15-2005, 05:05 AM

Did you put the code in ThisWorkbook code module as stated in original
posting.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Kim" <[email protected]> wrote in message
news:[email protected]...
> I've tried the last suggestion by Bob.
> The date and time doesn't change when I saved the worksheet.
>
> "Bob Phillips" wrote:
>
> > Also Andi, this seems to work no better. It doesn't even seem to save
it.
> > Disabling events helps but still leaves it after the event.
> >
> > But this works, better than the Ontime way
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > Application.EnableEvents = False
> > Cancel = True
> > ActiveWorkbook.Save
> > Application.CalculateFull
> > wb_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob,
> > >
> > > Would that not calculate it before it saves? Therefore it would show
the
> > > time of the previous save.
> > >
> > > Would this work:-
> > >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > > cancel = true
> > >
> > > Application.Calculate
> > > ActiveWorkbook.Save
> > > End Sub
> > >
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Good thought Andi, that should work.
> > > >
> > > > Kim,
> > > >
> > > > This is the code that Andi refers to
> > > >
> > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > > Boolean)
> > > > Application.Calculate
> > > > End Sub
> > > >
> > > >
> > > > 'This is workbook event code.
> > > > 'To input this code, right click on the Excel icon on the worksheet
> > > > '(or next to the File menu if you maximise your workbooks),
> > > > 'select View Code from the menu, and paste the code
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Andibevan" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Could you not also put calculate in the beforesave event? If you
> > > ordered
> > > > it
> > > > > correctly you could actually get it to calculate after it had
saved
> > the
> > > > > file?
> > > > >
> > > > > "Bob Phillips" <[email protected]> wrote in
message
> > > > > news:[email protected]...
> > > > > > This is because saving a file does not trigger a worksheet
> > > calculation,
> > > > > and
> > > > > > it doesn't update when there is some calculation triggered by
other
> > > > > changes,
> > > > > > so your function doesn't update. You can force a recalc with
> > > > Ctrl-Calc-F9,
> > > > > > or you can add Application.Volatile to the function so that it
gets
> > > > > > recalculated on an F9 or when some other change occurs that does
> > > trigger
> > > > a
> > > > > > recalc.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "Kim" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > I created a function in Excel to display the "Last Saved Date
and
> > > > Time"
> > > > > in
> > > > > > a
> > > > > > > cell. The code is as follows:
> > > > > > >
> > > > > > > <
> > > > > > > Function LastSaved( )
> > > > > > > LastSaved =
ActiveWorkbook.BuiltinDocumentProperties.Item(12)
> > > > > > > End Function
> > > > > > > >
> > > > > > >
> > > > > > > After saving the modified file, the cell displays the "Last
Saved
> > > Date
> > > > > and
> > > > > > > Time". I tried to modify the file another time and saved it.
The
> > > cell
> > > > > > however
> > > > > > > does not change to the new date and time. But this was
reflected
> > > under
> > > > > the
> > > > > > > File -> Properties -> Statistics.
> > > > > > >
> > > > > > > How can I update the cell to continuous display the right date
and
> > > > time
> > > > > > > whenever the "Save" button is clicked ?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >