log user's who access an excel file

[kj1977]

Is there any way to trace or log users who open an excel file on our shared
server? We have reason to believe that someone who we trust is passing
confidential infromation to outside sources about our business numbers and
would like to track who is in that file and when etc. Trying to sniff out a
rat basically.
Thanks in advance.

 

[Mike H]

Hi,

Setting aside whether something as unsubtle as this would work or the number
of ways to get around this working, the code below will record the username
of anyone who open the workbook and record the time. I would suggest you
record the information somewhere a little less obvious then column 1 of sheet
1!!

Alt+F11 to open VB editor. Double click 'ThisWorkbook' and paste this code
in on the right

Private Sub Workbook_Open()
With Sheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(lastrow, 1) = Environ("Username")
.Cells(lastrow, 1).Offset(, 1).Value = Now
End With
ActiveWorkbook.Save
End Sub


Mike

 

[Gord Dibben]

You can track users by login name if you allow macros to run when the
workbook is opened.

The login name will be placed in a cell on a veryhidden worksheet which only
you can access.

If macros are disabled, make the workbook unusable.


Gord Dibben MS Excel MVP

 

[Gary''s Student]

You would need to capture the information immediately after a user opened the
file, otherwise the file could be opened, data copy, and the file closed
without saving. Include a special tab called "log". Enter the following
event macro in the wrokbook code area:

Private Sub Workbook_Open()
Sheets("log").Activate
If Range("A1").Value = "" Then
n = 1
Else
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
Cells(n, "A").Value = Environ("username")
Cells(n, "b").Value = Date
ActiveWorkbook.Save
Sheets("Sheet1").Activate
End Sub

Let’s make sure it’s in the correct place.

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (workbook code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Darren Wolny]

Can you modify this so it lists only the last 100 users so as not to make the file huge as people use it?

Thank You