Creating a stamp of who opened a file in directory

[AmyTaylor]

Hello everyone, hope someone knows how I can progress with this:
I would like a piece of code that would save some information whenever a file in a specific directory was opened.
The directory is: "s:\referencedocuments\files\"

I would like a log file to save the following details:
the name of the file (could be any of 50+ excel spreadsheets,
when the file was opened
the name of the user who opened the file (based on NT login)
when the file was closed
time in minutes when the file was open.


Is this at all possible. I have used something similar using Workbook_Open and Workbook_BeforeClose for getting this information for individual files but I would really like something that could be run for any file in the directory.

Thanks for any help
Amy xx

[colofnature]

There are two ways I can think of, both of which are a pain to implement:
1. Put your _Open and _BeforeClose code in each of the workbooks in the folder. You wouldn't have to do this by hand if you don't mind trying out this sort of stuff: http://www.cpearson.com/excel/vbe.htm
2. Arrange for all the users who have access to this location to have their Personal.xls files updated with event handlers to check if they're opening a file in the location in quesiton.

Either way, the actual writing to a log file is trivial enough. Just a nightmare getting it setup... but if I had to chose one or t'other, I'd go for option 1 myself.


Col

[Mallycat]

There is a good tip here, but you will need to add the code to every sheet.
http://www.j-walk.com/ss/excel/tips/tip95.htm It also uses the username field which may not be accurate enough for you