Time the Workbook was last modified

[SammyB]

The FileDateTime function and the Scripting.File.DateLastModified both give the Date/Time that a workbook was last saved. Is there a way to get the Date/Time that a Workbook was last edited (but not saved)? TIA

[HansV]

The File object has three date properties:
DateCreated
DateLastAccessed
DateLastModified
Unfortunately, the DateLastAccessed property appears to be unreliable. For a workbook that I currently have open, DateLastAccessed is equal to DateLastModified (when I last saved it yesterday) instead of the time I opened it.
I'm not sure the info you want is available...

[SammyB]

Thanks Hans. I just wanted conformation of what I suspected.

[ChrisGreaves]

... a Workbook was last edited (but not saved)?

Hi Sammy.
I have at this time (FWIW 8:57a.m. in Newfoundland) opened a workbook in which I record meter readings.
By "edited" do you mean:
(1) Examined by my eyes or
(2) The <F2> key being used to load the editing box or
(3) The contents of at least one cell being modified by tapping the <E ter> key after using the <F2> key?
(4) Other well-defined event (please specify) e.g. Hans's post below)
Thanks, Chris

[SammyB]

have auto-save off
do 3
do not save
leave it open for a day
run a macro which displays yesterday's date/time when you did 3
If you can do that, please share the code

[ChrisGreaves]

have auto-save off
do 3
do not save
leave it open for a day
run a macro which displays yesterday's date/time when you did 3
If you can do that, please share the code

Hi Sammy; I don't write code (for a solution) until I have understood the problem.
Are you saying that (3) is what you mean when you say "edited"?
Thanks, Chris

[SammyB]

Yes (3) is what I mean when I say edited.

[ChrisGreaves]

If you can do that, please share the code

Edited.png

Sammy, here is a screenshot of VBA code in Excel2003 which displays a message box when a cell is changed in that worksheet.
You might like to try running the code to make sure that the message box appears whenever you "Edit" the worksheet in whatever way you define "edit" to be. I tried (a) over-typing the contents of the cell and (b) editing the cell with the <F2> key.

Once we have agreed that a worksheet change can be detected, then we can work on the second part which you stated as "run a macro which displays yesterday's date/time when you did 3".
Cheers, Chris

[SammyB]

The change event works, but this is not an acceptable solution. It must be a macro in my Personal Macro Workbook. Also, the Change event is only for a worksheet, not the workbook.

[HansV]

It's possible to write code that does something similar for all worksheets in all open workbooks. But it probably still wouldn't do what you want - for example, it wouldn't catch recalculation, which also changes a workbook...

[ChrisGreaves]

NOTE: I posted and forgot to add the XLS, Edited to add the XLS. Then discovered I hadn't saved the xls, deleted and re-added. Sorry! C

The change event works, but this is not an acceptable solution. It must be a macro in my Personal Macro Workbook. Also, the Change event is only for a worksheet, not the workbook.

SammyB, thanks.
I take "The change event works," to mean that the MsgBox popped up, showing that we can detect a change in a worksheet, and that we can detect changes which you would define as being an "edit".

This means that we are partway towards satisfying your request in your initial post in this thread. ("Is there a way to get the Date/Time that a Workbook was last edited (but not saved)?")

Attached is an updated copy of the “Edit.xls”.
Please open the workbook and immediately inspect the File, Properties dialogue box.

Untitled.png

In Excel2003 it looks like this. You should be able to see FOUR custom variables, three from the help files and one from me; that’s the one titled “alpha”. Cancel the dialogue box and look at the VBA code (Alt+F11, Alt+<space>, C)

Locate the function strSaveDocumentVariable
At the foot of that function is a commented subroutine; drag the subroutine outside the function and then decomment the subroutine. When you run the subroutine it should display THREE popup message boxes and FOUR lines of data in your Debug Window (Ctrl+G)
I had to refresh my memory to write this, so tried the VBA code from the Help Files. I left my Help File code in the function, commented out. You will delete those initial commented lines once you have located the corresponding example in the Help Files for future reference.

The function is written as three parts:-
(1) Does the proposed custom property exist? If it does, save its value in a local variable strResult
(2) If the proposed custom property does not exist, then create it.
(3) Set the value of the property and return the previous value (if any) as a result)

This function demonstrates that we can save metadata of our own in the workbook.

The function contains many vulnerable points; it is written as a proof-of-concept, and will need to be made more robust. But right now I am interested only in demonstrating that there is a way to do this. This answers your question "Is there a way to get the Date/Time that a Workbook was last edited (but not saved)?"

Like me you will probably have to play with CustomDocumentProperties while you get the hang of it. For starters you might want to add a FOURTH call to the function and set the property of “alpha” to be “epsilon”, save and close the workbook, then open it, run the TEST subroutine, and check File, Properties again.

At this point you should be able to:-
(a) Put that original code Worksheet_Change in each worksheet of your workbook. Each worksheet can use its own CustomDocumentProperties Variable to store the (string) formatted date/time of the last change/edit to that worksheet
(b) Write a couple of Workbook functions to locate the latest date/time in the worksheets and store that in a new CustomDocumentProperties Variable called, perhaps, “WorkbookLastEdit”

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_Open()
End Sub

The change event works, but this is not an acceptable solution. It must be a macro in my Personal Macro Workbook. Also, the Change event is only for a worksheet, not the workbook.

These are new constraints added after your initial post. I am still responding to your initial post.
Your original post reads “The FileDateTime function and the Scripting.File.DateLastModified both give the Date/Time that a workbook was last saved. Is there a way to get the Date/Time that a Workbook was last edited (but not saved)? TIA” and yes, although today (Monday) we are saving the file date/time inside the workbook, which implies that the workbook needs be saved, you can record the date/time elsewhere and obtain it from any program in any language you choose.

But first let’s make sure that we can do the basic tasks. Yes?
Cheers, Chris

[ChrisGreaves]

... it wouldn't catch recalculation, which also changes a workbook...

Hans, thanks for today's "Thought for the day"
Cheers, Chris
P.S. I found this little gem, which lets me off the hook because in Excel2003 ...

Code: Select all

I think the trick you need to implement (if you're application runs in Excel 2007 or later) is to handle this with the Application.AfterCalculate event, which is raised after both calculation is complete and there are no outstanding queries.

Cheers, Chris

[HansV]

I'd be wary of using event procedures related to calculation - they're "expensive" since they get called a lot.

[ChrisGreaves]

I'd be wary of using event procedures related to calculation - they're "expensive" since they get called a lot.

I agree. But then there were no execution time-constraints specified in the original post.
I think that if execution time becomes an issue AND recalculation is to be monitored THEN I/we need to look at a better solution.

Right now, by definition, we are interested ONLY in a DateTime result of my event (3) "The contents of at least one cell being modified by tapping the <Enter> key after using the <F2> key" which, to my mind, avoids the immediate question of recalculation. Trapping a worksheet-change event, where that event might cause recalculation, is a possibility, but it is sufficient to trap that worksheet-change event to satisfy my (3).

I am not expressing that very well, but it'll have to do until I have had lunch!

I suppose that recalculation is normally implied after any change; that's what makes spreadsheet processors so attractive, IMHO.

I'm not trying to weasel out of this, but the original post was a little loose, and I believe that a valid answer to the original post would have been a simple "Yes".
Which, as usual, I have failed to do!
Cheers, Chris