Create a log file of who has opened spreadsheet and when

**MarkFranklin** · 12-17-2008, 07:57 AM

Hi, apologies if you can find an answer to this question elsewhere, I know ther was something similar a couple of years ago, but I can not find it!

More detail is below, what I want is to have a macro which saves (to a txt file?) the user name and time/date they accessed the spreadsheet. So everytime the sheet is accessed it is added to the list..

More detail:
My boss is getting me to put together a spreadsheet that details when equipment was last checked and when it is due to be checked, lots of teams will be updating the data.

He wants me to save it to our network so other teams can see it and know when the equipment needs to be rechecked. My problem is that the existing sheets aren't kept up to date and I don't think people will update the sheet and only a couple of people will ever look. I am going to be forced to produce the sheet but want a way to show how often it is actually used...

Thanks
Mark

**royUK** · 12-17-2008, 08:15 AM

This code creates a list of user name & date & time in sheet1, which can be very hidden, on opening

Please Login or Register  to view this content.

**royUK** · 12-17-2008, 08:24 AM

If you need more security, you could create a log file in a directory

Please Login or Register  to view this content.

**davesexcel** · 12-17-2008, 08:50 AM

Here is a code supplied by Microsoft to get the user name
http://support.microsoft.com/kb/161394
I have modified the last part to add the name to a sheet

Please Login or Register  to view this content.

Delete the msgbox line if you want.
The attached example works when you open the workbook, it automatically runs the code and hides the UserName worksheet
GetUserName.xls

**rubberducky** · 09-16-2011, 06:49 AM

Hi,

Apologies if this query isn't appropriate, but is it possible to tweak this vba code in order for it to work when a Word doc is opened? If so, I'd appreciate the code as I've searched everywhere with no joy and would find it very useful...

Option Explicit

Private Sub Workbook_Open()
Dim sSecretFile As String
Dim sUser As String
Dim sDate As String
Dim sSave As String
sSecretFile = "C:\UserLog.txt"
sUser = Environ("username")
sDate = Format(Now, "yyyy/mm/dd hh:mm")
sSave = sDate & vbTab & sUser
Open sSecretFile For Append As #1
Print #1, sSave
Close #1
End Sub

**royUK** · 09-16-2011, 09:11 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.