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.

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

"kj1977" wrote:

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.

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



On Mon, 16 Feb 2009 11:00:14 -0800, kj1977
wrote:

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.

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

Lets make sure its 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

--
Gary''s Student - gsnu200834


"kj1977" wrote:

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.