Creating a Log

[Patrick]

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").Unprotect 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").Protect 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

 

[Jim Thomlinson]

Excel does not deal with concurency at all well. You will have a lot of
difficulty avoiding collisions. Do you have MS Access on your computer. If so
Access is ideal for tracking this kind of info without any of the concurrency
issues. What you want to do is really pretty easy to impliment with ADO
recordsets. Let me know if you want to follow this path...

 

[Jean-Yves]

Hi Patrick,

Just check if the file if not open in read-only mode :
.....
boFileOpen = False
Do
Set wbDest = Workbooks.Open("QuestionnaireDB.xls", , , , conPW_DB)
If wbDest.ReadOnly = True Then
wbDest.Close
Application.Wait (Now + TimeValue("00:00:01"))
Else: boFileOpen = True
End If
Loop Until boFileOpen = True
......

Regards,

JY

 

[Patrick]

Perfect! Thank you JY!

:
Hi Patrick,
Just check if the file if not open in read-only mode :
......
boFileOpen = False
Do
Set wbDest = Workbooks.Open("QuestionnaireDB.xls", , , , conPW_DB)
If wbDest.ReadOnly = True Then
wbDest.Close
Application.Wait (Now + TimeValue("00:00:01"))
Else: boFileOpen = True
End If
Loop Until boFileOpen = True
.......

Regards,

JY

 

[Patrick]

I do have Access and I am interested. Until this point my company has been
using a different relational database product... but it looks like we are
heading toward Access. I should probably learn to use it... and this logging
task will force me to get off my rump and finally do it (with some help