tracking file usage

[tst]

Hello All,

Looking for a way to track who has accessed multiple Excel files in a share
folder including file name, time/date file was last opened, etc.

We've found "network monitoring software" that produces a text logfile of
everything we need but the software has to be installed on the file server
that houses the share and company policy does not allow this without a long
drawn out approval process. Exciting to know it at least can be done.

Has anyone been able to duplicate what this type of monitoring software is
able to do? Our windows server and computer security teams say they do not
log this type of user information. We're all running xP and Office 2007.

 

[Mike H.]

If you can have macros in the files then you could do it in this basic fashion:

When a user opens a sheet, the file would have a macro that opens a simple
text file and records the user's name (from the login) and the time/date.
Obviously they could open a file without running macros and then defeat your
purpose, but you could prevent that by protecting and hiding things so that
if they open the file without the macro, the would be seeing nothing! Code
for doing that is available already by searching the programming area. If
you need help with this macro, let me know. I have some code around where I
did this previously. I got it down to tracking more than just opening, but
also what things they did when in the file. But your situation would be way
more simple.

 

[tst]

Thanks for the quick reply.

Yes I'd really like to see the macro that you used. I've found a couple of
examples in the programming area but still trying to get them to work at this
point.

After looking at these examples, I'm a little concerned about a couple of
items:

1. If the macro writes the user info into the file being opened, I won't be
able to retrieve the data if they copy the file from the share to their own
machine before opening (which is what most usually do as they don't want to
accidently change the working file).

2. If the macro writes the user info to an external file on the network,
the information won't be captured (maybe even produce an error) if they copy
the file locally then open when not connected to the network.

Did you have to have workarounds for this?

 

[Mike H.]

Oft times I don't allow the file to open successfully if it is not named a
certain group of names and in a specific directory. That prevents any issues
like this. But if you wish to allow them to copy the file down, log off the
network and then work with the file, you have whole net set of issues. What
if two people do this and then replace the file. Won't one person overwrite
the other's work? I don't like the sound of this at all. So I wouldn't
allow it to happen. Anyway, if you do allow something like this, then what
you could do would be if the file is opened somewhere other than "J\approved
folder\approvedname.xls" then I would create the log in a hidden tab in the
sheet. Then when the file is "back at it's approved folder/name, I'd take
those contents of the hidden tab and place it out in my external file that is
a log of who opened the file. I could write code to do that too and include
it in the upload I'll have to get you later (probably next week as my week is
almost done).

 

[tst]

The hidden tab solution with the write to the external file log sounds great.
Looking forward to seeing your sample and thanks again for all your help.

 

[Mike H.]

I was out of town over the weekend but will try to get this in the next
couple days to you. As any good procrastinator, I have to do my income taxes
tonight as they are due for us late-filers on the 15th!