• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

several users write to an xls sheet at the same time

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
Thanks
Robbie
0
IP4IT Staff
Asked:
IP4IT Staff
4 Solutions
 
BrianWeCommented:
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
0
 
BrianWeCommented:
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

0
 
sstampfCommented:
Yes it is possible to share a workbook. See the following links to learn how to share a workbook:
http://www.associatedcontent.com/article/1460904/create_a_shared_workbook_microsoft.html
http://www.youtube.com/watch?v=wd2AVFSaqyU  (Video Tutorial)
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sstampfCommented:
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:
http://office.microsoft.com/en-us/excel/HA010130571033.aspx 
0
 
IP4IT StaffAuthor Commented:
Thanks lads for the prompt reply
Looks like ill be teaching myself access then
Thanks again for all your help
Greatly appreciated
0
 
Patrick MatthewsCommented:
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.

Regards,

Patrick
0
 
RayneCommented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now