S
Stonewall Rubberbow
I’m having a sharing issue that I hope I can get some advice on. I’ve
got one workbook that’s shared between 4 or 5 computers; shared settings are
set to not keep a change history and auto-update every 5 minutes. Then I’ve
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 workbook’s data while the shared workbook is considered
“lockedâ€.
I think I’m 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. I’ve 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 won’t go away (or it seems like it won’t go away when it’s actually
popping up the browser for each piece of data it’s trying to access). What’s
worse is that all 20 of the non-shared data-pulling workbooks will go into
the same state. The shared workbooks are locked, can’t be accessed, and one
of them usually has a “sharing violation†error message. The times this has
happened (about once a day), we’ve had to shut down all of the Excel windows
to get it to release the shared workbook and then reopen the files.
I’m not very experienced here, but in my reasoning and from what I’ve
read, there’s 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.
got one workbook that’s shared between 4 or 5 computers; shared settings are
set to not keep a change history and auto-update every 5 minutes. Then I’ve
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 workbook’s data while the shared workbook is considered
“lockedâ€.
I think I’m 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. I’ve 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 won’t go away (or it seems like it won’t go away when it’s actually
popping up the browser for each piece of data it’s trying to access). What’s
worse is that all 20 of the non-shared data-pulling workbooks will go into
the same state. The shared workbooks are locked, can’t be accessed, and one
of them usually has a “sharing violation†error message. The times this has
happened (about once a day), we’ve had to shut down all of the Excel windows
to get it to release the shared workbook and then reopen the files.
I’m not very experienced here, but in my reasoning and from what I’ve
read, there’s 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.