Code to create a log to show who has opened or amended an Excel file?

Question

Code to create a log to show who has opened or amended an Excel file?

Anonymous

Hi. I would like to create a log in an excel file that will shjow me who has opneded and file and alos whether they made any changes? I do not want to know what changes are made just whether theere are any. Does anyone know if this is possible?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

8 additional answers

Answer 1

OK, here is code that you would need to put into the "ThisWorkbook" event processor for each workbook in the folder that you wanted to track. What it does is that instead of writing to a sheet in the workbook (you can throw away LogSheet now), it writes to a text file which I've chosen to give a .csv filetype notation to (LogFile.csv) that will either be created (the first time anyone opens a workbook after you implement this code in a workbook) or will append to that file after it has been created. It makes records in the file like this:

"Opened:","F:\JLATHAM\ExcelWork\TestFolder\Logit.xls","JLatham","Wednesday, May 18, 2011 13:44"

"Changed:","F:\JLATHAM\ExcelWork\TestFolder\Logit.xls","JLatham","Wednesday, May 18, 2011 13:46"

Now the slick thing about that file is that you can open it with any text editing file such as NOTEPAD, or by its format and .csv name, you can even open it in Excel and it will put the type of action (opened/changed) into column A, the filename into column B, the username into column C and the date/time (as text, not real date time) into column D. Now it's easy for you to group by specific file to see who has been into it, who made changes to it, or who is accessing which files by filtering/sorting the data.

Here is the new code.

Private Sub Workbook_Open()

Const logFile = "LogFile.csv"

Dim logFileName As String

Dim fileBuffer As Integer

logFileName = ThisWorkbook.Path & Application.PathSeparator & logFile

fileBuffer = FreeFile()

'just in case their is a log jam caused by multiple users

'trying to access the log file at the same time

'ignore the error (record may not be written to the txt file)

'but this file won't crash on them because of it either

On Error Resume Next

Open logFileName For Append As fileBuffer

Print #fileBuffer, Chr$(34) & "Opened:" & Chr$(34) & "," & Chr$(34) & ThisWorkbook.FullName & Chr$(34) & "," _

& Chr$(34) & Application.UserName & Chr$(34) & "," & Chr$(34) & Format(Now(), "dddd, mmmm dd, yyyy hh:mm") & Chr$(34)

Close #fileBuffer

If Err <> 0 Then

Err.Clear

End If

On Error GoTo 0 ' let system or other error trapping function normally

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Const logFile = "LogFile.csv"

Dim logFileName As String

Dim fileBuffer As Integer

Static changeRecorded As Boolean

If changeRecorded Then

Exit Sub

End If

'just in case their is a log jam caused by multiple users

'trying to access the log file at the same time

'ignore the error (record may not be written to the txt file)

'but this file won't crash on them because of it either

On Error Resume Next

logFileName = ThisWorkbook.Path & Application.PathSeparator & logFile

fileBuffer = FreeFile()

Open logFileName For Append As fileBuffer

Print #fileBuffer, Chr$(34) & "Changed:" & Chr$(34) & "," & Chr$(34) & ThisWorkbook.FullName & Chr$(34) & "," _

& Chr$(34) & Application.UserName & Chr$(34) & "," & Chr$(34) & Format(Now(), "dddd, mmmm dd, yyyy hh:mm") & Chr$(34)

Close #fileBuffer

If Err <> 0 Then

Err.Clear

Else

changeRecorded = True

End If

On Error GoTo 0 ' let system or other error trapping function normally

End Sub

Answer 2

I'm a little confused. If you give them a file named log to save in the folder, we are back to the original concept of letting the Excel file use/create one in the folder it is running from - which takes us back to the problem of 'what if the user saves it on their desktop'.

So here's what I've done with the code now: I have changed it to look for the LogFile.csv file in the folder that the Excel file is running from, and if it does not find it, it does not try to use it at all. This prevents log files from being created on the user's desktop. Of course no use of the file will be recorded. I suppose this can be informative in one indirect way: if a user's name doesn't appear in the log file at all, you will know they either aren't accessing any files or are using a desktop/local system copy.

Here's what to do at your end:

Open up NOTEPAD and make one entry that looks exactly like this -

ACTION,USER,FILENAME,DATE/TIME

End the entry with the [Enter] key. Save the file as LogFile.csv. Check the filename on disk after you save it, if you aren't careful it may end up named LogFile.csv.txt. If it does end up named that way, just rename it to remove the .txt from the end.

Then you can send a copy of that file and the Excel file out for the department heads to place on their servers for use. I hesitate to use just plain "Log.csv" as the text file name because many programs use a "Log" file such as Log.dat or Log.txt and someone else may use Log.csv, so I'm trying to keep it at least a little unique.

Now here's the code that will work in the fashion described above:

Private Sub Workbook_Open()

Const logFile = "LogFile.csv"

Dim logFileName As String

Dim fileBuffer As Integer

logFileName = ThisWorkbook.Path & Application.PathSeparator & logFile

If Dir$(logFileName) = "" Then

Exit Sub ' not running from server folder

End If

fileBuffer = FreeFile()

'just in case their is a log jam caused by multiple users

'trying to access the log file at the same time

'ignore the error (record may not be written to the txt file)

'but this file won't crash on them because of it either

On Error Resume Next

Open logFileName For Append As fileBuffer

Print #fileBuffer, Chr$(34) & "Opened:" & Chr$(34) & "," & Chr$(34) & ThisWorkbook.FullName & Chr$(34) & "," _

& Chr$(34) & Application.UserName & Chr$(34) & "," & Chr$(34) & Format(Now(), "dddd, mmmm dd, yyyy hh:mm") & Chr$(34)

Close #fileBuffer

If Err <> 0 Then

Err.Clear

End If

On Error GoTo 0 ' let system or other error trapping function normally

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Const logFile = "LogFile.csv"

Dim logFileName As String

Dim fileBuffer As Integer

Static changeRecorded As Boolean

logFileName = ThisWorkbook.Path & Application.PathSeparator & logFile

If Dir$(logFileName) = "" Then

Exit Sub ' not running from server folder

End If

If changeRecorded Then

Exit Sub

End If

'just in case their is a log jam caused by multiple users

'trying to access the log file at the same time

'ignore the error (record may not be written to the txt file)

'but this file won't crash on them because of it either

On Error Resume Next

fileBuffer = FreeFile()

Open logFileName For Append As fileBuffer

Print #fileBuffer, Chr$(34) & "Changed:" & Chr$(34) & "," & Chr$(34) & ThisWorkbook.FullName & Chr$(34) & "," _

& Chr$(34) & Application.UserName & Chr$(34) & "," & Chr$(34) & Format(Now(), "dddd, mmmm dd, yyyy hh:mm") & Chr$(34)

Close #fileBuffer

If Err <> 0 Then

Err.Clear

Else

changeRecorded = True

End If

On Error GoTo 0 ' let system or other error trapping function normally

End Sub

Answer 3

You are brilliant! This is so clever! I have one more question now though! Whilst playing around I tried saving a shortcut to desktop and actually saving a file to desktop. I have already asked users NOT to save files on desktop but some still will! Is there a way to amend the code so we do not create the Log File on people's desktops but just in folders?

Also I tried to password protect the Log file - I realise that somone could delete the file but I only want managers to be able to view it - but the individual managers would neeed to set password once the log is in the folder. I know I am askig a lot but this is so helpful!

I really appreciate the help!

Answer 4

What you have given me does exactly what I asked for but you have now got me thinking! Users will be creating copies of the file in a shared folder - could I set up a file in the folder that would create a log for each file and tell me who had accessed each one, time and date and did they make changes?

Thanks!

Answer 5

Do you want the log contained in that workbook, or in another file such as a text file?

The code below assumes you want it in that workbook. You'll need to add a sheet to the workbook named "LogSheet". You can even make that sheet hidden after you add it.

This is easily defeated by a user electing not to permit macros to run when the workbook is opened.

Then you need to copy all of the code below and put it into the Workbook's 'ThisWorkbook' event processing module.

To get to the right place in the VBA project:

Open the workbook.

Press [Alt]+[F11] to open the VBA Editor (VBE).

Press [Ctrl]+[R] to make sure the VBA project is visible. It will have a title like "Project - VBA Project"

Either double-click the "ThisWorkbook" object in the list in the VBA Project pane, or right click it and choose [View Code]

Copy the code below and paste it into the code module presented to you.

Close the VBE.

Save the workbook and close it.

Open the workbook and take a look at the "LogSheet' - you should see an entry there that you opened the workbook and on what date/time you opened it. If you make a change on any sheet other than the "LogSheet" itself, then the phrase "Made Change(s)" will appear in column C.

Private Sub Workbook_Open()

'prevent error from sheet filling up with log entries

If Worksheets("LogSheet").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row >= Rows.Count - 1 Then

Worksheets("LogSheet").Cells.ClearContents

End If

Worksheets("LogSheet").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Application.UserName

Worksheets("LogSheet").Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Now()

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name = "LogSheet" Then

Exit Sub

End If

Worksheets("LogSheet").Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = "Made Change(s)"

End Sub

Share via

Code to create a log to show who has opened or amended an Excel file?

8 additional answers