Determine if a file has been updated

[Digita]

Hi everyone,

When a XL file is shared among a group of users. Some would make changes to it and others open it just to view and exit excel without updating any info.

Apart from the usual techniques such as: tracking changes via XL or using workbook change events, comparing the physical file last saved date to that of the original backup file or a time consuming technique of running a subroutine to compare the contents of each individual cell to the original backup copy, would there be any other way to determine if the file has been tampered with?

Thanks in advance for your time and input in solving this question.

Regards


KP

[mdmackillop]

Why not add a WorkbookChange macro to write the date/time either to a specific cell or to Custom Document Properties (File/Properties/Custom). You need to add the chosen property before you can write to it.
eg
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ActiveWorkbook.CustomDocumentProperties("Date completed") = Now
End Sub

Sub Macro1()
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.CustomDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = p.Value
rw = rw + 1
Next
End Sub



On second thought, maybe BeforeSave rather than Change, as all changes may not be saved.

[Digita]

Hi MdMacKillop.

I tested it & got "runtime error 5 - invalid procedure call or argument". This error occurs when XL is unable to run the following line



ActiveWorkbook.CustomDocumentProperties("Date completed") = Now


Further examination of the file properties in the statistics tab, the dates document created, modified and assessed have been greyed out. I think that the error could be due to user rights restrictions rather than the actual code. I'm using file on a LAN environment.

Another alternative I can think of is insert user name and date when the following test is true. This test is carried out in the workbook beforesave event.



if thisworkbook.saved = false then ...........


I think I can figure it out from here.

Thanks for your help as usual. Have a great weekend.

Best regards


KP

[Bob Phillips]

Don't forget though if you have any volatile functions, such as =TODAY() in your workbook, this will 'dirty' the workbook upon opening, and set the Saved flag to False.

[Digita]

Thanks XLD for the tip. Have a nice day.

Kind regards


KP