Shared workbook prevents other workbooks from opening

[ciag]

Greetings,

I have a team of 5 users working out of one shared workbook which is about 10MB in size. The formulas are not all that plentiful nor are they complex. They are experiencing issues as of late, they are unable to open any other excel documents while working in the shared workbook.

They are also running into difficulty if they click on the 'file' tab within excel. when this happens the excel completely hangs and has to be shut by task manager, this second issue is less common. I thought this might be a network mapping issue/'location' issue but all users only have the basic local network drives and printers mapped.

We are using excel 2010 but all the computers involved are still running XP. I have other users using different shared workbooks of similar size who don't hit these issues.

what can I check or change to help these user out, all suggestions welcome?

Regards

Ciaran

[Kyle123]

My first suggestion is not to use shared workbooks they are notoriously unstable, easily corrupted and buggy - there are other solutions that work much better available, have a look at google docs or access

Try going back to an earlier version, it's quite possible that the workbook has become corrupted, then update the earlier version with the latest data

[:) Sixthsense :)]

I like working with shared workbook

My Suggestions:

1. Keep the File in xlsb format if possible.
2. Daily basis on End of Day (When No user is connected with the workbook)
   remove the sharing and reshare it
   Clear the unused rows and columns with Alt+E+A+A which clears unnecessary temps.
   which will reduce the file size considerably and removes the unnecessary cluster too..
3. Ask the users to press Ctrl+S before/after doing any entries.
4. DON'T let one user to open the file in Excel 2003 version and another one in 2010 which cause SEVERE DAMAGE TO FILE even leads to DATA LOSS

If you follow all these then I am sure your file won't get corrupted or you won't face any issue with it

[ciag]

Brilliant,

I will get working on that thank you, great tips!!

Thanks

Ciaran

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback.

Please don't forget to comeback again and share your feedback after a couple of days or weeks to let us know whether the above suggestions helps you or not. Because I am seeing many is against the Shared Workbook feature, but it will be a good if you share your experience may be even the feedback is good or bad. If it is bad feedback then it will be helpful for me so that I stop redirecting questioners with my above suggestions. If it is good then I will get a proof that shared workbooks are not bad for multi user environment.

[Kyle123]

How many opinions would you like?

http://blog.contextures.com/archives...ooks-in-excel/

[:) Sixthsense :)]

How many opinions would you like?

@ Kyle123,

Thanks for sharing the link

But it depends upon the usage of the persons and the options what they need. Some of them may use it for simple data entry purpose and using the file in multi user environment with 5 persons won't be a big hurdle according to my experience with shared workbook.

I am seeing many just simply saying Don't use shared workbook without knowing the persons usage.

have a look at google docs

I don't know what is the option which makes people to think of google docs instead of SkyDrive

Does google Docs is better than SkyDrive? If so please have a look in the attached SkyDrive Screenshot file.

SkyDrive.JPG

[Kyle123]

Dunno, I've never used skydrive; if it's webbased and not desktop based then I'd imagine it will work better than Excel at any rate I suspect that the reason that google docs is suggested more is due to pricing.

My point was that actually, there are much better options of which two possibles are google docs and access. Excel simply isn't designed for use in a multi user environment, so I wouldn't use it when there are better alternatives. For simple data entry I'd pitch access (or any other database) over Excel any day - databases are designed from the ground up to support multiple users so handle concurrency properly. Conversely given enough time pretty much all shared workbooks will develop problems, though you are correct, workbooks that are simple, contain only data in a tabular form do fair better, but back to my original point - why would you choose Excel when there are better alternatives?

[:) Sixthsense :)]

I'd imagine it will work better than Excel at any rate

I have created a file and uploaded it in both skydrive and google docs and taken the screenshots and you can find the difference how the google docs and skydrive works with the excel spreadsheet. Please have a look in the below screenshots

Google Docs With Pivot.jpg

SkyDrive With Pivot.jpg

I suspect that the reason that google docs is suggested more is due to pricing.

Even both google docs and skydrive allow you to add files using your individual email id and you can share it with other and users can do it without paying any amount I believe

My point was that actually, there are much better options of which two possibles are google docs and access

Still suggesting google docs, since skydrive is also free to access . But from my point of view many don't give access to INTERNET to employees and about the other one ms-access I believe not all excel users are aware how to work with access.

Excel simply isn't designed for use in a multi user environment

It can be used in multi user environment with some limitation in head count (Not like access) and with proper maintenance it can be used and that's why Microsoft has made shared workbook concept I believe

I wouldn't use it when there are better alternatives

I suggest available possibilities to the OP's in the application they are comfortable with it. If nothing is worked for the OP then in the final stage I go with your above quotes.

why would you choose Excel when there are better alternatives?

Good question Taking back to my olden memories

You believe it or not almost 40 to 50 persons (2003 file) use a workbook in a shared mode and don't follow the right methods when working with the shared workbook. Some of them may give save before entering data some of them may not some of them keep the cursor in Editing mode (F2 in a cell) always. Lot of data conflicts happens even hourly basis. For a day 2 to 3 revised workbooks are created and old workbooks (Corrupted Workbooks or Locked Workbooks) taken in to backup folders.

I thought to create a Access Database in which these problems will be getting vanished and spent time in designing the data base and created it by Tables|Queries|Forms and all and done everything and secured the database with User Level Security by applying New Data Users/Full Data Users and Admin Rights and I thought everything is ok and released the file for use.

Ahh!!! I never knew my database will get a worst feedback in a hours time and it will get ended. You know I did a small mistake curious to know right? You are aware in Access Form there is a property called Record Locks which will be in default stay as No Locks I am not aware of that property and kept it in NO LOCKS the form show the entries which has been completed by the data entry person. So the Audit person will pick that entry and do the audit and put their Employee Id and Date. What happened is 5 to 6 persons has picked the same record and entered their Employee ID and date and tried to exit the record (Exitting the record which will show to others as the entry has been picked by someone) but the problem is due to NO LOCKS in RECORD LOCK property 5 to 6 persons entered their employee id and time (time in another field) and try to move from that record which made access to get a LOCK since access don't know which entry needs to be retained

Yeah! At that time I am unable to understand the root cause of the error and fix it and everyone is just stopped the work and waiting for the file to continue the work then the management waited for 10 minutes and said go back with excel and continue your work and No More Ms-Access file is tolerated.

Ha ha this is the one of the reason I won't suggest people to go for another application in which they are not comfortable with it. Because at that time I am not aware of the Record Lock issue and which given a bad result to me, so I try to suggest the available possibilities with the application the OP's are comfortable with it

[Kyle123]

The notion of a shared workbook with 50 users offends me as to access, then yes, it is really designed for a smaller set of users when you would concurrent editing. So I wouldn't suggest access either for 50 users, Id suggest a proper database SQL server, mySQL etc, both of which have no cost (though the express edition has limitations) you can use access (or excel) as the front end and only one user needs it installing - if you get concurrency issues with that, you've done something really wrong

As to what the user is comfortable with, just because someone knows how to use an application doesn't mean that it's the right or even a good solution.

[:) Sixthsense :)]

Id suggest a proper database SQL server, mySQL etc, both of which have no cost (though the express edition has limitations)

Hm.. The fact is that I don't know anything aboout the above quoted sentence and it's beyond my knowledge so I can't go for the unknown approach

We split up the work, like you keep on showing the right application to their need and I will continue to suggest available possibilities to make it in excel, If it not possible in excel then me too suggest the other possibilities (But on end stage only)

Thanks for sharing your valuable thoughts

[bluesoftsbl2]

Hi you.
i know a way that can help you. I think it is the best way for you ti fix your problems. It is a add-in A-tools in Excel, it can help you to share your excel file in LAN or in Internet. In addition, it can help you to authorization for the other computer, what can connected to open what kinds of Excel file, what kinds of sheets, which have authorization to edit or only view, copy or print?