Append text to log file

At work I once maintained 3 workbooks on a shared drive with stop statistics. Two of the statistics were basically identical, and I dislike doing unnecessary work.

I also suspected that nobody used the data in the third workbook, so I decided to log if any other user than myself opened the workbook.

After quite some time the log had no entries at all, so I told my boss about it and said that I intended to stop maintaining the workbook.

He laughed and said okay. After all he could hardly insist on me wasting time.

Below is the simple macro that does the job by appending text to a text file. For the code to work, the text file "log.txt" must exist in the same folder as the workbook, but you can easily change that.

To copy the code highlight it with the mouse, press CTRL+C and paste it into a VBA module with CTRL+V.

Sub auto_open()
'Executes when the workbook is opened.
'Call the procedure AppendToTextFile or
'any other procedure.
End Sub

Sub AppendToTextFile()
'Opens a text file, if the username is different
'from whatever, and appends date and time plus
'username. In this way you can log other
'people's access to a workbook or a document.

Dim fs, f
Dim sPath As String

'Change "Eric Bentzen" to your own username.
If Application.UserName = "Eric Bentzen" Then Exit Sub
On Error Resume Next

'Assumes that the log file is in the same
'folder as the workbook. Of course you
'can change this and the name of the log file.
sPath = ThisWorkbook.Path & "\log.txt"

Set fs = CreateObject("Scripting.FileSystemObject")

'Opens the file - the number "8" is the constant
'for appending text.
Set f = fs.OpenTextFile(sPath, 8)

'Insert line break, time and username.
f.Write vbNewLine & Now & " " & Application.UserName

'Close the file.
End Sub