0

I need to know when an Excel workbook was last modified after it was saved.

Lets say I saved the file, and after I saved it, I modified something that triggered the workbook.Saved = false. How to get the timestamp of this last change made to the workbook ?

I am aware of the ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") but this only gives me the timestamp of the saved file and does not take in account any modifications made after the save.

Anyone know if this is possible? I found nothing online, and no BuiltinDocumentProperty has this information.

cyberponk
  • 1,585
  • 18
  • 19
  • 1
    You could keep track of it with the [`SheetChange`](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.sheetchange) event maybe. – BigBen Apr 15 '20 at 16:11
  • It is an option, but unfortunately this does not track any other changes such as VBA code changes, for example. There are many other workbook changes that trigger the `workbook.saved = false` and do not fire the `SheetChange` event – cyberponk Apr 15 '20 at 16:13
  • I don't have any other ideas then... you may be out of luck, maybe not, hopefully not. – BigBen Apr 15 '20 at 16:15
  • 1
    There is nothing like it, no. Why do you need this? You could hack up something with handling `SelectionChange` and `SheetActivate` to evaluate whether `.Saved` is `False`; I wouldn't think that's reliable, but probably the closest you can get. – Mathieu Guindon Apr 15 '20 at 16:16
  • I am working on an automatic backup module, and need to know if I already have a backup for the latest edits before user saves the file. – cyberponk Apr 15 '20 at 16:20
  • Why not just make a new backup regardless? – BigBen Apr 15 '20 at 16:22
  • I could, but my system does a backup every 5 minutes, so then even if I the user doesn´t make any change, I will have 20 backups/hour, which in a full work day will take me around 2gb because of the workbook size. It is an option but I am hoping to find an alternative solution that makes a backup only when the workbook was actually modified but not saved – cyberponk Apr 15 '20 at 16:26
  • You can condition the backup by preliminary checking of `.Saved` property, then... So, the backup will be done only if `.Saved` property returns `False`. Supposing that the backup is done using VBA... – FaneDuru Apr 15 '20 at 16:39
  • 1
    Do you know git and is turning autosave on an option? If so, you could hack up git source control to handle the backups (repository could be a private, remote repository on GitHub), basically treating the workbook binary diff as a changeset/commit, and then you could easily "restore" the workbook "as of" any of these commits - and your file system only ever needs a single copy of the file. Actually you could hook it up to a AfterSave handler and make a new commit/"checkpoint" every time the file is saved, through autosave or not. – Mathieu Guindon Apr 15 '20 at 16:43
  • 1
    Since you can calculate MD5 on a file using VBA: make a backup, then check the MD5 for that backup against the previous backup: if there's no change then delete the backup. – Tim Williams Apr 15 '20 at 17:15
  • 1
    MD5 in VBA: https://stackoverflow.com/questions/2826302/how-to-get-the-md5-hex-hash-for-a-file-using-vba – Tim Williams Apr 15 '20 at 17:17

0 Answers0