3

I have an automated program which is working on Excel workbooks. When someone opens the workbook currently being processed, they will get a read-only message. If multiple users/programs try to open a file at the same time, all of them open it successfully. Still, only the last saved changes will be kept, messing up the whole process.

So the question is if there is any way to configure an Excel workbook to be capable of simultaneous editing with any changes appearing immediately to all other users?

Office 2010-2013

  • 2
    As the answers say, Excel is the wrong tool. You don't say what Office version you have, but if it's Professional it contains MS Access. That's a database, so suitable to the job. Learn how to use that and update it from your application. – Jan Doggen Mar 21 '14 at 9:16
  • There is anyway to connect the excel file to access but its enough if i work in excel? kinda database related – user2859137 Mar 21 '14 at 9:30
2

Well, I found some answers relating to your question. I'm quoting one of the answers, but there are lots of pages via google search, which answer your question.

Quote from ExcelForum.com

under share workbook editing tab check the allow changes by more than on user ..... box warning this may not work as you require!

sharing like this causes all sorts of problems

if more than one user changes same cell on save they will be prompted "whose changes to keep" now if they choose theirs the other gets deleted

if they chose other they will have to reenter date elsewhere also sharing is prone to cause workbook bloat a smallish 1meg file can swell to 15 meg + quite easily.

the work book often can lose data for mysterious reasons and formatting changed seemingly on a whim.

users forget to close workbook and when you look at who has this file open you could end up with 20 or so names many of them duplicated.

so dont share unless you are in strict control of it.

unshare it everynight 9this clears out odd users)

save a copy at least once a day.

it really works best if only one user is likely to edit a field at anyone time

say a job list where user one is dealing with job one so no one else is likely to update that field because they would be on say job 3.

if you can avoid it do so

you have been warned

Other sources: 1. Office.microsoft.com 2. Office.microsoft.com

| improve this answer | |
6

Office365 can accomplish this to an extent. However, there are still some limitations to real-time collaboration--i.e., you do not always see the other's users updates immediately.

A good alternative is Google Sheets, as it was built from the ground up for real-time collaboration.

| improve this answer | |
  • I've found this to be the only feature where Sheets really beats Excel: real-time multi-user collaboration. – Engineer Toast Nov 2 '17 at 12:35
2

The answer you maybe don't want to hear and that toots into the same horn as mk117's is: Excel is not really the right tool for that task. Yes, many people before you have tried similar things and they all run into the same issues. Excel is a spreadsheet tool, not an IDE for data entry applications. Yes, you can accomplish a lot with it, but in the end you always come across issues with concurrent access to the file and or data. If you have that option, create a real application with a database in the background.

| improve this answer | |
1

If you need a simultaneous editing with any changes appearing immediately to all other users, in other words, real time update, I recommend you to try RowShare, a collaborative online table designed for sharing and collaboration. Feel free to have a look at the article I wrote: https://www.rowshare.com/blog/en/2017/11/09/Sharing-Excel-Sheet-Different-Rows-to-Different-People-Easy-Peasy

Thanks!

| improve this answer | |
  • 1
    Rowshare seems like a REEAAALLLY cool tool. – 123456789123456789123456789 Dec 5 '17 at 18:25
  • Can you please post the general ideas from your article? That way if rowshare ever disappears, then this answer stays valid. – Eric F Dec 5 '17 at 19:18
0

This is not possible at the file level. You could use an abstraction layer that synchronizes access to spreadsheets. Programs that need access to these files would then direct their calls to a network service instead of opening the file.

| improve this answer | |
0

Check out MySQL for Excel. It's a free product and offers real time collaboration with INSTANT changes. Of course, you'll need to administer a back end DB but it enables Excel to become a very flexible front end and a true network database.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.