Excel question

Is there a way to have a cell read the last date/time the spreadsheet was changed? We have a number of people who access the same spreadsheets, and the spreadsheets change numerous times throughout the day (scheduling spreadsheets). The person who does the spreadsheet doesn't want to change the date/time manually each time he updates it. I know it can be done in the header/footer, but we only look at the file, and don't want to print it just to see if it's different.

Well the only way I know is that if you select the folder the Excel files are in and change the view to "Details" that should tell you when the file was last modified.
Rightclick > View > Details

But thats probably not much help to you.

It's been a very long time since I've played with Excel and other spreadsheets, but it seems to me that back in the dark ages it was possible to create a macro to automatically get the current date and time and write it in a subfile. Things may well have changed since then.

edit: Having just looked at a newer version, it should be possible to have a macro autoexecute and drop the current date and time into a cell. If you want to see the last time a file was changed w/o opening the file, that's an easier matter, easily done in the gui.

Mad1 said:

Well the only way I know is that if you select the folder the Excel files are in and change the view to "Details" that should tell you when the file was last modified.
Rightclick > View > Details

But thats probably not much help to you.

Click to expand...

That's actually part of the way to get it into the header/footer, but I can't find any way to pull that into a cell.

Malpaso said:

Is there a way to have a cell read the last date/time the spreadsheet was changed?

Click to expand...

Right click on the Excel workbook icon just to the left of the File menu option, near the upper left corner of your screen. Choose the View Code option, and paste this in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheet1.[A1] = "File was last saved " & Now
End Sub

Got that from here. I tried it and it works. You can change "Sheet1.[A1]" for the sheet cell location you want the info to be displayed in.

You can also use a macro to display the cell properties (I kinda like this one myself):

Sub LastModified_LastSavedBy()
'Keyboard Shortcut: Ctrl+Shift+L
'put last modified date/time in current cell
'put who last saved the file in next cell down
On Error GoTo err_Sub

ActiveCell.Value = "Last Modified: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
ActiveCell.Offset(1, 0).Value = "Last Saved by: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last author").Value

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:

Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: LastModified_LastSavedBy - " & _
"Module: Module2 - " & Now()
GoTo exit_Sub
End Sub

Great! Thanks guys.

The timestamp.html info might work but will take a bit of time. Using Now() will not work because that is simply the current date and will show even if no change is made.
You could put a button on the sheet and run a macro from that which inserted the date and closed the file.

The macro I pasted gives you:
'put last modified date/time in current cell
'put who last saved the file in next cell down

you just need to add the button routine, or program it to run on close or something like that.

I'm glad this came up. greenLED, do you know what is the macro to insert the tab (active worksheet) name in a cell? This feature is easily done on the Header and Footer, but I can't get the same information in a cell. Any help will be appreciated.

Thanks!

Not off hand, but Google popped this up:
http://www.mrexcel.com/archive2/29300/33399.htm


...a little more diggin':

Function WorkSheetName() As String
ActiveSheet.Activate
WorkSheetName = ActiveSheet.Name
End Function

haven't tried that one, though.

greenLED,

Thank you for helping. Excel took the macro, but I can't get it to appear in a cell.