Looking for opinions on Shared Workbooks

[zombiemaster]

Hi, All...

Background: I have a LAN-based Shared workbook that occasionally becomes corrupted after users go in and change formatting and add highlights. When this happens, I need to do some manual workaround for my VBA to run the next day. The "shared" aspect is mandated by management and I do not have any control over that piece...grrr...arrgh...

I did a general search online for "what can cause a shared excel workbook to become corrupted" and found a couple of cut-and-dried responses:

"All shared workbooks become corrupted -- it's a poorly implemented feature. The solution is to not use shared workbooks."

"There are two types of shared workbooks, those that are corrupted, and those that soon will be!"

I am just curious if users on this forum have the same opinion of Shared Workbooks? And has anyone discovered the secret to preventing formatting changes from making the corruption happen in the first place?

Let the debate begin!

Thanks for any input!
~ZM~

 

[My Aswer Is This]

My first computer Job was in the USAF correcting user damaged Spreadsheets.
They would delete formulas by entering data over the formula. Then in desperation after deleting the formula. Pull out their handy calculator to get the answer and put that in the cell till I was able to fix problems.

And these were not shared.

The way to solve these problems are to keep up to date backups.

And keep the backups some place where users cannot get to them.

 

[Gerald Higgins]

In my experience, one thing that can help is using worksheet protection, with passwords if necessary.
It's not foolproof, but it can help alot.

Another thing that might help - try spending time with the other users to see how they actually use the file.
It may be that you THINK you know how they SHOULD use it, but for all kinds of good and bad reasons they actually use it in completely unexpected ways.

 

[jmacleary]

Hi ~ZM~

I also inherited some shared workbooks a while ago, and I can honestly say: BAAAAAAAAD. They are extremely prone to corruption/lock-out and it is a nightmare to recover from. I hope you get enough comments on here to take back to your management and show them how bad shared workbooks are!

 

[dmt32]

Hi,

Speaking personally, I would always avoid shared workbooks. In situations where users needed to share data I would create template copies of a master workbook (database) & have these read write data to it. This approach requires some VBA but I did not experience any of the issues you describe.

You can read another opinion here:https://contexturesblog.com/archives/2008/11/18/avoiding-shared-workbooks-in-excel/

Dave

 

[Yongle]

Post#2 on this thread may be of interest if you are looking for a workaround
https://www.mrexcel.com/forum/excel-questions/1084052-workaround-excel-sharepoint-many-users.html

 

[zombiemaster]

Thanks for the input, everyone!

I should also mention for clarity: this is a daily report that gets run via a VBA (read-only) template I created. The VBA generates a newly-created workbook each day, with new daily data pulled from a data text file. The VBA saves the previous day's report in an "archive" folder, then takes the new data and creates pivot tables, splits the data into separate tabs based on the billing rep name pulled into the report and re-saves the report so the reps can go in, pull their daily work and add notes, highlight and format as necessary and also allow management to go in and see the progress being made throughout the day if needed. There are usually between 10-13 reps each day who have access to the file, plus 2-3 managers.

At one point I discovered that one particular user was hiding columns - every day - and that was causing the file corruption EVERY DAY for a week. Once I realized, I told her to stop doing that and things got better...but now it's happening again (not every day) and there are no hidden columns. People use a lot of highlighting, which I saw somewhere else can cause problems as well but here is unavoidable since that's how they keep track of their finished items.

Again, thanks for everyone's input and I'm not looking for solutions (I know there aren't any for this) as much as just javing a general conversation on the subject.

~ZM~

 

[Yongle]

- give EVERY user their very own workbook that runs a macro to simply OPEN the master and pull the data etc into their workbook and close the master WITHOUT saving it

 

[jmacleary]

- give EVERY user their very own workbook that runs a macro to simply OPEN the master and pull the data etc into their workbook and close the master WITHOUT saving it

I don't think this meets the OPs need for managers to get real time updates on progress - they would only see the start of day position.

 

[Yongle]

@jmacleary is correct

- give EVERY user their own workbook that runs a macro to simply OPEN the master and pull the data etc into their workbook and close the master WITHOUT saving it
AND ...
- update the manager's workbook with data from individual workbooks (via macro) as often as is required

Structure of individual workbooks should be protected to prevent users merging cells, moving columns etc