several  users  write to an xls sheet at the same time

Posted on 2010-01-06
Last Modified: 2012-06-27
Hi all I have  windows server 2008
I have an xls document on the server
Several   people need to be able to write to this document at the same time
At the moment when the first person opens the xls sheet they get read and write
When the second person opens the xls sheet they get read only access is there a way to fix this so several  users can write to this xls sheet at the same time
Question by:IP4IT Staff
    LVL 2

    Accepted Solution

    Simple answer is no.. You should really be working with a database if you need record-level locking (which you do to do what your trying)

    The longer answer is that Excel 2007 has some change control features that let you "check out" the file for short periods, make changes and then "check in", allowing another user to then check out the file..

    This requires sharepoint services.. and if you go into Excel 2007 and hit F1, then search for "Using Excel Services to share data while maintaining one version of the workbook" you'll get a full explanation

    Hope this helps
    LVL 2

    Assisted Solution

    Ok.. Supplemental research gives a "sort of"

    This is a direct quote from the Excel Help file

    "Allowing multiple users to edit a workbook simultaneously
    If you have a workbook in which you want to allow more than one other person to add or modify data simultaneously, you can set up and save the workbook as "shared" and then make it available on a network share (not a Web server).

    Use the Share Workbook feature when:

    You want multiple users to be able to edit the data in one workbook simultaneously, and you are comfortable with the original data in the workbook being modified, including edits, additions, and deletions.
    You have a network share available on which to store the workbook and to which users have access.
     Note   More than one user cannot simultaneously make changes to a shared workbook that is stored on a Windows SharePoint Services 3.0 site. If you want to store your workbook on a Windows SharePoint Services 3.0 site, you should do so only after the collaboration effort through sharing is complete.

    You want to keep a record of the changes that are made in the workbook.
    You do not expect to change the following features, which cannot be modified after a workbook is shared: merged cells, conditional formats, data validation, charts, pictures, objects (including drawing objects), hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros.

     Important    Saving an Office Excel 2007 shared workbook to an earlier version file format unshares the workbook, and the revision history that documents the conflicts and resolutions is lost. If you need to save the workbook to an earlier version format, you may want to save a copy of the revision history first.

    As the owner of the workbook, you can prepare it to be shared by entering and formatting the data that needs to be present. When you are finished, click Share Workbook in the Changes group on the Review tab, select your options, and then save the workbook on a network share (not a Web server) that is available to the intended users. Alternatively, you can click Protect and Share Workbook in the Changes group on the Review tab to prevent other users from turning off Change Tracking in the workbook.

    Each time that you save the shared workbook, you are prompted with the changes that other users have saved since the last time that you saved the shared workbook. If you want to keep the shared workbook open to monitor progress, Excel can update you with the changes automatically, at timed intervals that you specify, with or without saving the workbook yourself.

    When you save changes to a shared workbook, another person who is editing the workbook might have saved changes to the same cells. In this case, the changes conflict, and you are prompted with a conflict resolution dialog box so that you can choose which changes to keep."

    the above text in quotes is copyright Microsoft 2009

    LVL 12

    Assisted Solution

    Yes it is possible to share a workbook. See the following links to learn how to share a workbook:  (Video Tutorial)
    LVL 12

    Assisted Solution

    But I want to warn you that sharing a workbook is generally not recommended. It implements many restrictions to your excel file/features and may also lead to file corruption. I would recommend using MS Access in your case. See the following link to learn about Shared Workbook administration:

    Author Closing Comment

    by:IP4IT Staff
    Thanks lads for the prompt reply
    Looks like ill be teaching myself access then
    Thanks again for all your help
    Greatly appreciated
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello robbiebreslin,

    While you can share a workbook, as outlined above, you should take the following warning to heart:

    The consensus opinion of Experts here is that using 'share workbook' should be assiduously
    avoided.  This is a poorly implemented feature in Excel that leaves workbooks highly
    vulnerable to corruption.  If true multi-user capability truly is needed, then the best practice
    is to use a database platform such as Access, SQL Server, MySQL, etc.



    Expert Comment

    Damn....i told my boss lets go with access at the beginning of development of the solution but he insisted on excel....should have looked over this question 7 months ago :(
    Why do my eyes open up late?

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This article shows how to convert a multi-page PDF file into multiple image files, with one image file created for each page of the PDF. It does this by utilizing an excellent, free software package called GraphicsMagick. The solution is amazingly s…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    Sometimes we receive PDF files that are in the wrong orientation. They may be sideways or even upside down. This most commonly happens with scanned or faxed documents. It is possible to rotate the view of these PDFs with the free Adobe Reader produc…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now