Results 1 to 8 of 8
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    simple 'tamper' detection (Excel 2000)

    I have a VBA routine that extracts data from one file, A, to another Excel file, B.

    I have another user C that then opens the extracted File B

    I want to include something simple within File B that will alert User C if File B has been 'tampered' with.
    'Tampered' with means another User has opened File B directly, made changes, and re-saved the file.

    For example, let's say my File A VBA routine places a time-stamp code into File B at the time of creation.
    User C can then check whether the time-stamp code corresponds to the File B filedate timestamp.

    I don't particularly want to set File B passwords.

    Any suggestions??

    zeddy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simple 'tamper' detection (Excel 2000)

    You could put code into Workbook B's Workbook_BeforeSave event routine that would record every time the workbook is saved, possibly in sucessive cells down a column of a worksheet dedicated to this purpose.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: simple 'tamper' detection (Excel 2000)

    Expanding on Legare's suggestion, you can include the time/date and the username to the info to see who save it.

    Steve

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: simple 'tamper' detection (Excel 2000)

    Many thanks for your responses.
    I like the suggestions but I want to avoid code in File B.
    A User could simply open the file with macros disabled, adjust some cells and then save the file again.

    Similarly, if I apply a workbook password to File B I would have no way of subsequently knowing whether another User has managed to open File B with the password somehow and then made changes etc.

    The object is to detect whether File B (and contents) is the file actually created from the File A code and not one that has been 'manually adjusted' later.
    So my idea was to have the VBA code in File A put a 'hidden timestamp' (linked directly linked to File B creation date).
    My follow on question was:
    When does Excel actually timestamp a new file?
    Does it do it when the file has been completely saved ( e.g. a large file on a network)?
    Does it do right at the beginning?
    For example if I say, put a
    sheets(1).range("a1") = Now
    ..and then immediately following give a VBA workbook save command
    ..what is the likely discrepancy between the file's actual filedate timestamp and the contents of cell [a1] ?

    Thanks again for your thoughts!

    zeddy

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: simple 'tamper' detection (Excel 2000)

    Why don't you just use WIndows explorer?

    You can right-click on the file and chose properties. It gives the date created, last modified and last accessed?

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simple 'tamper' detection (Excel 2000)

    If you are worried that the user will open with macros disabled, then you could make all of the worksheets very hidden. Then in addition to the Before Save routine, also have a Workbook Open event routine that unhides the sheets. Then if they open it with macros disabled, they won't see anything to modify.

    About the only thing you can do other than this that is simple is to put the workbook into a network directory (I assume that you are on a network if more than one user can get to the file) that only User B has write access to.
    Legare Coleman

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: simple 'tamper' detection (Excel 2000)

    Hi Steve

    I ran a test using a large 7.5MB Excel file.
    I opened the file and just used File SaveAs to create a dummy copy and then immediately closed the file.
    When I examine the file properties of the dummy file with explorer there is a difference of 17 seconds between the file created date and modified date.
    This was done on my laptop. I suspect that the time differences could be larger if this was done on a 'sluggish' network.

    My problem is that if a User were going to 'adjust' a file they are likely to do this almost immediately after the File B is created by the VBA code.
    Because of variable network performance, I cannot see a clear indication from the file properties that the File B is indeed the one created by VBA in the File A processing.

    Legare's solution now looks best.
    Using a Workbook BeforSave I could increment a hidden counter to show that File B was saved more than once.
    This will get around the timing issues.
    And your suggestion of capturing the user details of who last saved is also very useful.

    Many thanks to you both!

    zeddy

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: simple 'tamper' detection (Excel 2000)

    Hi zeddy,

    Sounds like a case for invoking a set of strong read/write access permissions through the OS.

    You could also set the file's read-only attribute. If the suspect user's not all that savvy, that'll be enough to stop them changing the file.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •