Workbook Contention

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

I have an application that is used by several users concurrently, each new
user opens the workbook read-only, in this workbook a record of the action
they make are recorded, upon closing the application an email message is
sent to the administrator providing the usage statistics for that user. The
workbook closes and all changes are lost. This all work fine.

With increased usage the administrator would prefer to be mailed a summary
workbook with all usage statistics being already compiled. My question is
two fold.

1. I can create a summary workbook into to which all users activites are
written as the application closes, but how can I deal with the contention
that arises from simultaneous users trying to write their data?

2. Do I have to open and close the summary stats workbook in order to write
the data - since I imagine the contention is more of a probelm if the
workbook is already open and another tries to open it. If I do need to open
it can I test if another uses has it open and then wait for it being
released before the new user takes control?

.......or is there another better way?

TIA
Cheers
Nigel
 
You might use a routine like this one to update the summary workbook:

Sub a()
Dim WB As Workbook
Dim Done As Boolean
Application.DisplayAlerts = False
While Not Done
Set WB = Workbooks.Open("c:\wb.xls", ReadOnly:=False, Notify:=False)
If Workbooks("wb.xls").ReadOnly Then
WB.Close False
Application.Wait DateAdd("s", 1, Now) ''Wait a second
Else
Done = True
''Write changes
WB.Close True
End If
Wend
End Sub
 
Thanks Jim, I read this as every second it will try to open the workbook as
not readonly, until it does not in which case changes can be written. Nice!
I'll need to put some messages in for the user, but I don't think the volume
of changes and incidence of contention wil be that high so it should work
great.

Thanks Again
Cheers
Nigel
 
Back
Top