Excel Function for Retrieving SaveDate?

BSmiley
BSmiley used Ask the Experts™
on
I'm looking for a way to include the date a workbook was saved into a cell in an Excel spreadsheet.  Using the current date/time isn't sufficient, it needs to be the date the file was saved.

I've looked for excel functions to do this and am coming up empty.  Any help would be appreciated.  I don't mind using VB if necessary, as long as it will be treated as part of a formula and will be recalculated as such, seemlessly.

Thanks in advance.
Bob
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
Commented:
Hello BSmiley,

you could try

-open the vb editor with alt+F11
-next look for the thisworkbook icon on th left pane and double click it
-now in the code pane paste this code

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Sheets("Mysheet").Range("A1").Value = Now()
End Sub

-this will put the value of now in the cell on your sheet every time the file is saved
-chang eit to your needs
-save and close the editor

HAGD:O)Bruintje

Author

Commented:
Excellent.  It wasn't what I was asking for, but it goes to show that sometimes the user (in this case me) doesn't know the best approach.  Instead of what I was asking for, you gave me something that works even better.  And I learned a lot within the mystery of how VB and Excel integrate.

Thanks so much for the super-fast response.
Bob
Top Expert 2006

Commented:
Thank you for the grade ...

yes think this is also the best way to start in looking to VBA.

-you got a simple requirement
-you can think it through
-then you look for the correct parts in code

and voila after three times testing it works :)

Commented:
Bruintje,

As usual I found what I was looking for and your name was on it.  I have one question to add -

Can this solution be used to display the data in a protected cell?

Thanks in advance!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial