Excel

Catch The Company Spy - Log Dates & Times That a Workbook's Been Opened

Ease of Use

Easy

Version tested with

2000 

Submitted by:

johnske

Description:

Creates a text file on your hard drive. In this example, the text file logs all the dates and times that a workbook has been opened. (The zipped example also contains further procedures that allow you to log workbook changes and sheets printed if it's found that there has been some regular tampering) 

Discussion:

Ever suspected that some unauthorized person has been accessing a file? These files may contain sensitive personal or business data, exam questions, results and such-like. This allows you to check - by looking at an activity log on your hard-drive - whether the workbook's been opened at a time when you may not've even be present. It is not completely secure against those that have a good knowledge of VBA and the ability to open locked projects but it can certainly catch out the more ordinary spies. The LogInformation function allows you to act with the full knowledge of the date and time it was opened (and also that the file HAS actually been accessed without your knowledge)... NOTE: There are many alternative uses for this function, by changing the code regarding what activities need to be logged, you could also use this to simply create a notepad/wordpad synopsis of activities such as contracts that've been finalized, or, (say) a backup of all items that've been deleted or modified. (N.B. To change the Notepad log to a larger Wordpad file, simply change the log.log in the code to log.frx) 

Code:

instructions for use 

			
 '***********CODE FOR MODULE1***********
Option Explicit 
 
Public Function LogInformation(LogMessage$) 
    On Error GoTo MakeFolder 
Entry: 
    Open "c:\MyLogFiles\" & Left(ThisWorkbook.Name, _ 
    Len(ThisWorkbook.Name) - 4) & _ 
    " Log.Log" For Append As #1 
    Print #1, LogMessage 
    Close #1 
    Exit Function 
MakeFolder: 
    MkDir "c:\MyLogFiles" 
    Resume Entry 
End Function 
 '**************************************
 
 
 '*****CODE FOR THISWORKBOOK MODULE****
Option Explicit 
 
Private Sub Workbook_Open() 
    LogInformation "Opened by " & Application.UserName & _ 
    " " & Format(Now, "dd mmm yyyy hh:mm:ss") 
End Sub 
 '**************************************

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Tools/Project Explorer
  4. Select the ThisWorkbook module
  5. Copy and paste the ThisWorkbook code into this Module
  6. Now select Insert/Module
  7. Copy and paste the Module1 code into this Module
  8. Now select File/Close and Return To Microsoft Excel
  9. Dont forget to save your changes...
 

Test the code:

  1. Close the workbook and re-open it
  2. Go to your hard-drive (C) and find the folder 'MyLogFiles' and the text file (Your workbooks name here) and "LogFile" inside the folder
  3. Open the text file and you will see something like this "Accessed: 02 May 2005 11:44:49"
  4. Every time you open the workbook a new date and time will be appended below the previous entry.
  5. NOTE1: Locking your project will give better protection from spies with a little knowledge of VBA coding.
  6. The attached workbook has further example procedures that allow you log whether there have been any alterations made to the workbook and if anything's been printed, but a complete log such as this is not recommended for everyday use as it would fill up pretty quickly and would need to be deleted regularly (when deleted, a new one will be created automatically). However, knowing what changes have been made may allow you to narrow any suspects down even further. :o)
 

Sample File:

Logger.zip 13.38KB 

Approved by mdmackillop


This entry has been viewed 576 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express