Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Contention
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 ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Contention
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 -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Contention
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 "Jim Rech" wrote in message ... 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 -- Jim Rech Excel MVP ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refresh pivot table in workbook A when changing a cell in workbook | Excel Worksheet Functions | |||
Adding a chart to large workbook brings workbook activity to a hal | Excel Discussion (Misc queries) | |||
Excel-how to link source workbook to copy of destination workbook | Excel Worksheet Functions | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
What commands do you use to name a workbook, save a workbook,open a workbook | Excel Programming |