report workbook's date last modified when the workbook is opened

[craigexcel]

I am using a system where Excel workbooks are attached to a 'request'. As I just found out this (accounting) monthend, these requests (including the attached workbook) can apparently be modified, but this can't be determined just by looking at the 'request' as presented in the system. I did some searching here, and since it appears VBA is real solution to try to get at this info, I tried a few snippets of code I found here last night, but without success. What I tried always returned the current date. Clearly I'm not skilled enough in my understanding of objects, etc to be able to navigate these waters.

One that I did try, since it seemed it would be very straightforward, was this:
Range("L1").Value = Format(ThisWorkbook.Builtin DocumentProperties("Last Save Time"), "short date")

Also, I can't do something like navigate thru a network folder of all these workbooks, and get the 'last modified date' of each, because this request system is a stand-alone system. But of course, maybe someone might have some ideas on that too.

Assuming VBA is the way I need to go, the code will need to be saved in a '[VBA] tools' workbook I created with various utility macros in it, which I will have open when accessing the 'requests' in this system. I'd like to be able to open the workbook attached to the request, run the code from my 'tools' workbook, and have the macro insert the 'last modified date' of the 'request's' workbook in some TBD cell in the target workbook. Then, if that date is < yesterday, then no further action (i.e. updates) need to be recorded.

I'm using Office 365.
Appreciate any thoughts you may have, or redirecting me to another thread maybe I've already read thru, but perhaps didn't have enough context or knowledge to understand.

 

[Fazza]

it sounds like the request system should do this itself - suggest you get the owners of the system to do what is needed

 

[craigexcel]

Agree the system should do this itself. But the reality is the system is a bit dated and doesn't have that capability, so we're stuck with using this system as is, as painful as that is.

 

[Fazza]

the situation/set-up isn't clear - I don't know how much understanding of the system is needed.

so, only guessing on this - maybe something upstream can change so that workbooks intrinsically record the data needed. either code in the files themselves - or an add-in if suitable

 

[Jaafar Tribak]

ThisWorkbook.Builtin DocumentProperties("Last Save Time") should in theory give you the date the workbook was last saved.
Does the workbook have some vba code in it behind the scenes that performs a saving upon opening ?

 

[craigexcel]

@JAAFAR -- No, neither my 'tools' workbook, nor any of the target source data workbooks, have any code that performs a save upon opening the workbook.
Will have to keep digging on this one. And, maybe there's some additional infor I can provide that might be helpful, but will need to look at that tomorrow.
Thanks for your reply.