Display last saved date in cell in Excel 8

[nigelpull]

How do I show, in a cell, the last date and time that the excel file was saved? The "now" function only shows the time and date that the file was opened, not saved.
If VBA is needed please be very descriptive since I have never used it before.

 

[DaleWatson123321]

This will work...

Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub


I hope this helps. Please advise as to how it works.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[nigelpull]

I still need help where do I enter:

Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub

How do I reference it in a cell?
Does it update automatically?
Can you send me an example, please?
I have never used VBA.

 

[DaleWatson123321]

nigelpull,

I'm answering this from home, so if you've emailed me at my work address, I'll send you an example file in the morning.

In the meantime, to get you started, here's what to do:

1) To open the &quot;Macro&quot; window, hold down the <Alt> key and hit the <F8> key.

2) At the top, under &quot;Macro Name&quot;, type any characters - e.g.: &quot;asdf&quot;.

3) Hold down <Alt> and hit &quot;C&quot; (or click &quot;Create&quot; - on the right side of the Macro window).

You should now be in the &quot;Visual Basic Editor&quot; window. Another method of going to the &quot;VB Editor&quot; window, is to hold down the <Alt> key and hit <F11>.

The reason for referring you to the <Alt> <F8> option first, is because by using that method, it will automatically create a &quot;Module&quot; for you. These Modules are sort of like separate &quot;folders&quot; where you can place various VBA (Visual Basic for Applications) code or &quot;routines&quot;.

Normally, once a user has created a LARGER number of various subroutines (the example I posted earlier is a &quot;subroutine&quot, then it can be advantageous to &quot;organize&quot; your various subroutines into these &quot;folder-like&quot; Modules. But for starters, only be concerned about using the one Module - named Module1.

Be aware, however, that when you &quot;record&quot; a macro, the macro will normally be placed by Excel into a separate Module - e.g. Module2.

Another reason for pointing you to the <Alt> <F8> option, is because it is here that you can click on &quot;Options&quot; - and be able to assign your macro to a &quot;keyboard shortcut&quot;. This means that you'll be able to activate your macro by, for example, holding down the <Control> key and hitting the letter you specify - e.g. &quot;Q&quot;. Naturally, you should AVOID using letters that are normally used for regular shortcuts such as <Control> B for Bold, <Control> S for Save, <Control> C for Copy, <Control> X for Cut, <Control> V for Paste.

Now back to the &quot;steps&quot;...

5) Highlight the routine I previously posted - here it is again, so highlight the following, and &quot;Copy&quot; it using <Control> C.

Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub

6) At this point, you should ALSO have your Excel window open, and therefore use: <Alt> <Tab> to go from this Tek-Tips window to your Excel window.

Note: If you happen to have multiple windows open, you might have to &quot;cycle&quot; through your open windows until you come to the right one. To do this, you &quot;keep&quot; holding down the <Alt> key, while hitting the <Tab> key until you come to the proper window. Then release the <Alt> key.

7) Highlight the &quot;temporary&quot; subroutine that you initially created, and &quot;Paste&quot;, using <Control> V.

8) To leave the &quot;VB Editor&quot; window, use <Alt> Q.

9) Now assign a keyboard shortcut to the macro you've just created. Do this by starting with: <Alt> <F8>.

10) Now use <Alt> O to choose &quot;Options&quot;, and then under where it says &quot;Shortcut key:&quot;, enter the letter &quot;q&quot;. Note that this is a lowercase &quot;q&quot;. If you enter a (capital) &quot;Q&quot;, then Excel will show you &quot;Ctrl+Shift&quot; to the left of the &quot;Q&quot;. This means that to activate your macro, you'll then need to hold down BOTH the <Control> key and the <Shift> key while you hit the letter &quot;Q&quot;.

11) Click &quot;OK&quot; (or <Enter>) to exit the &quot;Macro Options&quot; window.

12) To exit from the &quot;Macro&quot; window, click the &quot;X&quot; in the top-right-corner of the window.

You are (finally) ready to run your macro.

The macro is written such that the Date and Time Last Saved will be placed into the &quot;Active&quot; cell - meaning whatever cell your cursor is on.

Therefore place your cursor on the cell where you want the Date/Time to be entered, and THEN activate your macro - by holding down the <Control> key and hitting &quot;Q&quot;. (I show a &quot;capital&quot; Q because that's how it's displayed on the keyboard.)

If the column width is not wide enough, you will get ######## in the cell. This of course means the cell width needs to be made wider to display the Date and Time.

A final option...

If you should want to place the Date/Time Last Saved into a &quot;specific&quot; cell - separate from where your cursor is located, then you should modify the one line of the code. Here's the &quot;modified&quot; routine...

Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
Range(&quot;dtsaved&quot.Value = SaveData
End Sub

It is IMPORTANT to note that the reference to &quot;dtsaved&quot; above, refers to a cell that has been assigned this name as a &quot;range name&quot;. You can assign any name of your choosing, so long as you change the name in your VBA code to match.

Method of Assigning a Range Name:
a) Highlight a cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

Before someone &quot;accuses me of writing a book&quot;, I'll end this, and hope that you have been able to follow along.

I hope it's helped. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Dale - no-one could ever accuse you of giving a less than thorough response ;-)
Rgds
~Geoff~

 

[nigelpull]

That response was better than a book. Custom made for me!
Thank you for your time and patience.

 

[nigelpull]

It worked!
I am trying to keep a list of various issues together in a list. This list is changed often and by different individuals. I was hoping to create a SS with a cell at the top that will always display the last time the file was saved, to save some confusion.
Is there a way to invoke a macro to run when the file is opened?

 

[DaleWatson123321]

~Geoff~,

Thanks for the complement ;-)


nigelpull,

You asked... &quot;Is there a way to invoke a macro to run when the file is opened?&quot;

You could take the previous routine and use it in a &quot;Workbook_Open&quot; event. There is one addition that should be made. The modified routine below includes two new lines regardin the &quot;path&quot; of the ActiveWorkbook. This will be required in situations where the file previously open was in a DIFFERENT folder.

Sub Get_SaveData()
pth = ActiveWorkbook.Path
ChDir (pth)
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub

HOWEVER, there is a GOOD reason NOT to use the above as a Workbook_Open event. This is because, while the &quot;FileDataTime&quot; function provides the date/time the file was last &quot;accessed&quot;, it is ALSO AFFECTED and CHANGED when the file is opened - i.e. the date/time is UPDATED to the date/time when the file is opened.

Therefore, your BEST solution, is to use a Workbook_BeforeSave event... as follows...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
curtime = Now()
Range(&quot;dtsaved&quot.Value = curtime
End Sub

This will update the cell named &quot;dtsaved&quot; whenever the file is saved, and of course this will be an accurate record of the date/time last saved - whenever the file is next opened.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Important Update...

For anyone reading this thread, it's IMPORTANT to recognize the NEED to...

...place &quot;Workbook&quot; events (including a Workbook_BeforeSave or a Workbook_Open routine) in &quot;ThisWorkbook&quot; - instead of in a Module.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[elizabeth]

Dale, this is a great tip but as far as date range, I'd like to enter the 'last saved' date in the upper right section of the header. Is it possible to refer to this as a variable rather than a range?

 

[Eugene99]

Elizabeth,

This will work for you:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim curtime As String, DTSaved As String
curtime = Now()
DTSaved = curtime
With ActiveSheet.PageSetup
.RightHeader = DTSaved
End With
End Sub

Vr,
Eugene

 

[elizabeth]

Eugene, it is perfect! Took me less than one minute to implement! THANK YOU

 

[elizabeth]

I know this is somewhat off topic as the title of this thread is regarding Excel, but can I get advice on how to accomplish the same thing in a Word document? I guess this would be different as Word has one header section so I'd have to delete the previous date before entering the new.

BTW, I altered my Excel module to:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim curtime As String, DTSaved As String
curtime = Now()
DTSaved = curtime
With ActiveSheet.PageSetup
.RightHeader = "Last saved " & DTSaved
End With
End Sub

So the part of the header to be deleted would always begin with "Last saved" and continue through the end.

 

[DeLaMartre]

Dale, you ol' son-of-a-gun,

What brilliant and useful code from your typically brilliant self! Another star for the star!

It is truly a treat seeing you in the forum again.

Best Regards,

-Bob in California

 

[cLFlaVA]

elizabeth-

This is much easier in Word. I'm not sure about all versions of Word, but if you go to View > Header / Footer, you should be able to accomplish this.

Select Insert > Field from the Menu Bar. Under Categories, select (All) and in the Field Names box, scroll down to SaveDate. Select this, click OK. This will print the last save date (and time, unfortunately) for you.

Hope that helps.



*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[jonsi]

Excellent! Thanks Dale. I wasn't looking for this solution but now that you've given it, it's one I can use most definitely. You are a STAR!

Will your exemplar answer appear in the FAQs?

Jonsi B-)

 

[xlbo]

Jonsi - Dale's last reply on this thread was November '02

I doubt he'll see this now

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[jonsi]

wasn't looking at the dates Geoff ...just his reply. Even so ...worthy of a star nonetheless.

 

[xlbo]

Oh yeh - not doubting that - just doubting he'll see it to be able to "FAQ" it

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[elizabeth]

cLFlaVA, thanks! I was using only the preconfigured autotext fields and after spending lots of time searching through Excel I guess I got confused and thought I'd tried the lists in both.