Date Modified XL 2000 (SP3)

Topic

New Reply

Date Modified in the properties of XL workbooks appears to be the same as Date Accessed even if the last access is only a look and no changes were made; and date last saved does not seem to be available in VBA. Is this correct? If so is it possible to write a macro that records the correct date last modified or the date last saved?

TIA

Reply | Quote

Replies

Problem is that as soon as you open an Excel workbook, Excel changes the Last Modified date/time to the current date and time, so Last Modified and Last Accessed coincide.
When you close the workbook, Last Modified is either restored to the original value if the workbook isn’t being saved, or updated if the workbook is saved.
In other words, the Last Modified date/time is only accurate if the workbook is closed!

You can retrieve the Last Modified date of a closed workbook using Scripting.FileSystemObject or the function from post 682,264: GetProperty(“…MyFile.xls”, 3).

Reply | Quote

Thank you Hans

Only being able to access the info when the file is closed is a very useful feature.

I am not familiar with user defined functions, am I doing this right, I copied the function into a module in my workbook then in a cell in my workbook I typed:

=GetProperty(ActiveWorkbook, 4)

and I get a type mismatch error (same when I put the full path instead of ActiveWorkbook)

Reply | Quote

ActiveWorkbook can only be used in the Visual Basic Editor, moreover you should use ActiveWorkbook.FullName there. In a formula, you should use

=GetProperty(“C:ExcelSomeWorkbook.xls”, 3)

with the appropriate path and filename substituted.

Note that the path and filename are enclosed in double quotes “.

Reply | Quote