Logging users accessing a spreadsheet

  • Thread starter Thread starter DT
  • Start date Start date
D

DT

Looking for a way to log (on a sheet within a workbook) all users that
access the spreadsheet and when.

Is this possible using wither VBA or other method?

Many thanks.

DT
 
DT wrote...
Looking for a way to log (on a sheet within a workbook) all
users that access the spreadsheet and when.

Is this possible using wither VBA or other method?
Click to expand...

It's only be possible using VBA, and it's easily disabled since it'
easy to disable macro processing in Excel. Also, unless users save th
workbook, no permanant record would be kept.

Far better would be seeing if your network supports file-leve
transaction logging, in which case your network admins might provid
you with such logs for your workbooks
 
Do the users have access to a share that they can write to?

If yes, you could have a macro that adds a line to a tab delimited text file
when they open the file (macros must be enabled, though).

Rightclick on the excel icon to the left of the File option (on the worksheet
menubar). Select view code and paste this into the code window:

Option Explicit
Private Sub Workbook_Open()

Dim LogDir As String
Dim LogFile As String
Dim myFileNum As Long
Dim testDir As String

LogDir = "\\servername\sharename\foldername"
LogDir = "C:\my documents\excel"
LogFile = LogDir & "\log.txt"

testDir = ""
On Error Resume Next
testDir = Dir(LogDir, vbDirectory)
On Error GoTo 0

If testDir = "" Then
'not connected or spelling error!
Exit Sub
End If

myFileNum = FreeFile()
Open LogFile For Append As #myFileNum
Print #myFileNum, ThisWorkbook.FullName & vbTab _
& Application.UserName & vbTab & fOSUserName & Now
Close #myFileNum

End Sub

Then right click on the project (hit ctrl-r to see it if its not shown). Select
Insert, then Module.

Paste this in:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

=====
When you want to view the log, just open excel and do File|open--choose
delimited by tab when you see the import wizard.
 
Back
Top