I have a spreadsheet that demonstrated excess inventory. I need to be able to
keep track of who is using this spreadsheet... so I came up with the idea of
capturing some data (date, user, full path, how long they had it open) and
writing it to another spreadsheet on a network drive that everyone has access
to (P:\)
The code (below) works perfectly in my testing... right up until I tested
having 2 people open the file at the same time and close the workbook at the
same time. When that happened... my ExcessLog.xls file returned an "unable to
read file" error. I have not been able to open it since. Does anyone know
what this happened? Have any suggestions for making this work? Have a better
solution for keeping track of who opens/uses a spreadsheet?

Here is the code, which is in the ThisWorkbook object:

'** When workbook is opened triggers the start timer. When the workbook is
closed it captures the user, path, stop time after validating that the user
has access to the P:\

Dim TStart As Long 'Timer - Start
Dim TStop As Long 'Timer - Stop
Dim MyPath As String 'Full Path of Workbook
Dim PW As String 'Sheet Protection Password
Dim DV As String 'Dialog Value for MsgBox
Dim x As Long
Private Sub Workbook_BeforeClose(Cancel As Boolean)
PW = "test"
If Dir("P:\Permanent_Data\Patrick\Excess Inventory\") < "" Then
Application.ScreenUpdating = False
MyPath = Application.ActiveWorkbook.FullName
ChDir "P:\Temporary_Data_60_Days\Patrick\Logs"
Workbooks.Open
Filename:="P:\Temporary_Data_60_Days\Patrick\Logs\ ExcessLog.xls"
Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW
Range("A2").Select
x = 2
'** Get to empty row
While Trim(ActiveCell.Offset(0, 0).Value) < ""
ActiveCell.Offset(1, 0).Select
x = x + 1
Wend
TStop = Timer
Range("A" & x).Value = Date
Range("B" & x).Value = MyPath
Range("C" & x).Value = Application.UserName
Range("D" & x).Value = ((TStop - TStart) / 60)
Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW
Workbooks("ExcessLog.xls").Save
Workbooks("ExcessLog.xls").Close
Application.ScreenUpdating = True
Else
DV = MsgBox("Contact the HelpDesk and request access to the P:\",
vbOKOnly, "Slight Problem...")
End If
End Sub
Public Sub Workbook_Open()
TStart = Timer
End Sub


Any assistance/feedback is greatly appreciated! patrick