Im having a sharing issue that I hope I can get some advice on. Ive
got one workbook thats shared between 4 or 5 computers; shared settings are
set to not keep a change history and auto-update every 5 minutes. Then Ive
got about 20 non-shared Excel workbooks on other computers that pull data
from the shared workbook. All work is from Excel 2003. The workbooks that
pull data have a timer in VBA to accomplish the data pull every 2 minutes.
Everything runs as planned until the workbook pulling the data tries to
access the shared workbooks data while the shared workbook is considered
€œlocked€�.
I think Im seeing 2 different issues. One issue is that the non-shared
data-pulling workbook will try to access the shared workbook, get blocked in
doing so, and then open a browser to have the user find the shared workbook
(as if it has been moved or is missing). I have the users cancel out of the
browser dialogue and the workbook gets another update at the next Ontime
event. Ive been able to reproduce this issue by saving the shared workbook
at the same instant another workbook is trying to access its data. Normally,
saving the shared workbook only takes a second, and the non-shared workbooks
only take a second or two to run their code to pull the data, and this issue
seems to be happening far too often for it to just be a coincidence in the
timing.
The second issue is almost identical to the first, however, when the
user tries to cancel out of the browser dialogue, it pops back up immediately
and wont go away (or it seems like it wont go away when its actually
popping up the browser for each piece of data its trying to access). Whats
worse is that all 20 of the non-shared data-pulling workbooks will go into
the same state. The shared workbooks are locked, cant be accessed, and one
of them usually has a €œsharing violation€� error message. The times this has
happened (about once a day), weve had to shut down all of the Excel windows
to get it to release the shared workbook and then reopen the files.
Im not very experienced here, but in my reasoning and from what Ive
read, theres not much I can do on the shared workbook side because shared
workbooks are problematic, but there may be something I can do for the
workbooks that pull the data. Is there an error handling procedure I can
write into the macro that will skip the data-pulling process if the shared
workbook is locked? Does Excel even recognize this as an error? Also, if
someone has relative insight on why shared workbooks have sharing violations,
that would be much appreciated.

Thanks everybody for the wonderful advice. Okay, so my post was too long to
read; let me ask a simpler question. Can I write code that will check the
availibility of an external data source and skip the update if the source is
locked or utherwise unavailible?


"Stonewall Rubberbow" wrote:
Im having a sharing issue that I hope I can get some advice on... ...help would be much appreciated.