Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Shared workbooks in Excel (2003 and 2007)

    Having read previous posts I know they recommend avoiding shared workbooks, and agree, which is why I have only limited experience with them.

    However... I have inherited a situation and need to give clear, objective advice. As an Access developer, I can see that the nature of this data doesn't lend itself to a database solution – possibly project management, but I understand why they want to use shared Excel workbooks.

    Some users have Excel 2003, and mainly read the data, while others have Excel 2007 and are more likely to make changes.

    I was asked to help because of lost data issues(!), but found that the file was in .xlsx format, so Excel 2003 only allowed it to be opened as read-only, resulting in several messages before it displayed. In addition, one worksheet was too wide for Excel 2003.

    I split the worksheet in two and saved the file in .xls (97-2003) format, which eliminated the 2003 read-only problem. Furthermore, we don't seem to have had any data loss for two days.

    The major problem now is with custom views. This is what I would expect:

    - Workbook created by UserA, formatted, and set up with hidden columns, frozen panes etc
    - Workbook shared
    - UserB opens it and sees view saved by UserA
    - UserB hides a few columns and saves file
    - A view is created for UserB
    - If UserA opens file, will see UserA view
    - If UserB opens file, will see UserB view
    - But, if UserC opens file, which view would they see?

    This is what happened:

    - UserA unshared the file, converted from 2007 to 2003, split a worksheet (unhiding a number of columns), re-shared and saved, hid the original columns and saved again
    - UserB opened the file and found the columns unhidden, so hid them plus a few more and saved
    - UserA opened the file and found the ORIGINAL columns still hidden
    - UserC opened the file and found the columns unhidden, so hid the same columns as UserB and saved
    - UsersA, B and C were all able to open the file and see their own view of the hidden columns
    - Later, any user might open the file and see their last saved view OR all the columns unhidden again
    - If they call on their own view, nothing changes
    - If they call on UserA's view, they get back UserA’s set of hidden columns

    My search of earlier posts suggests that both the view inconsistencies and data loss are common. So:

    - Is there any reliable way to ensure that everyone gets the LAST SAVED view?
    - Is Excel 2007 any more reliable with shared workbooks?
    - Does the attempt to share between 2007 and 2003 introduce a whole new set of problems (eg different ways of handling the above or other sharing issues)?

    I think I know the answers but must present informed opinion (or a solution) rather than gut reaction!

    Thanks for reading this far...!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shared workbooks in Excel (2003 and 2007)

    > - Is there any reliable way to ensure that everyone gets the LAST SAVED view?

    Yes, unshare the workbook <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I don't know the answer to the other two questions, but how often will it *really* be essential that two or more users can edit the workbook simultaneously? If a workbook is not shared, the first user who opens it will lock it for editing, and subsequent users will see a warning, with three choices:
    - Cancel opening the workbook.
    - Open the workbook read-only.
    - Open the workbook read-only and receive a notification the moment the workbook becomes available for editing.
    So it is still possible for multiple users to view an unshared workbook, but since only one person at a time can edit it, you avoid the overhead and the potential conflicts that come with shared editing and that often lead to corruption of the workbook.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Shared workbooks in Excel (2003 and 2007)

    Thanks for your prompt feedback, Hans - unfortunately, they started off with single-use workbook, but really DO have a need for several people to be able to edit it at once - they are usually working in different bits of data, but need to keep it in the one workbook.

    I think I'm going to have to give them the choice between looking at the whole workflow in a completely different light, or putting up with the status quo!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared workbooks in Excel (2003 and 2007)

    Access seems more geared for this. It seems to save as it goes as the database is "opened" and used on the network as opposed to having a copy remaining on the server and several other copies in RAM on different computers all in various stages of change. It is not surprising to me that the files get corrupted if shared...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •