Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shared Workbook Tips (2000)

    Im putting together a FAQ on Shared Workbooks,

    Such tips as Deselecting the Track Changes , Archiving and creating a new sheet every now and then to keep the file size down

    was wondering if a few peeps on this board wouldnt mind sharing some more

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook Tips (2000)

    I have one which is not going to please you:

    Don't share workbooks.

    Excel is not good at this at all.

    If you need to share information, use a database program like Access or a web-based database application.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook Tips (2000)

    Believe me thats at the top of the list!

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook Tips (2000)

    While appreciating the entreaty not to share workbooks and repeating it as often as possible, as a desktop support person, I have inherited a business division who have invested heavily in the sharing of workbooks over the past few years. They haven't the expertise, (individually or collectively), resource or inclination to revisit all of their shared workbooks with a view to converting them to Access databases or to unshare them and nominate a custodian who will be the nominal "owner" of such sheets and perform all of the updates and revisions required.

    There have been a series of issues and problems arising from the use of SOME shared workbooks by SOME users on SOME occasions. In most cases, these issues have been resolved by unsharing the workbooks, but this is not an acceptable solution from the business' point of view. We currently use Office 97 but are shortly to upgrade to Office 2000, but I find in my searches that the sharing function isn't implemented with much improvement (if any) in the newer version.

    What I've been trying to find are some general troubleshooting principles that relate specifically to shared workbooks. All I can come up with, however, are "I wouldn't do that if I were you" responses. I couldn't even find much information on the downside of sharing from any Microsoft website, (which, on reflection is probably to be expected).

    Is there nothing out there?
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  5. #5
    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 Workbook Tips (2000)

    In my instances, for the most part, people do NOT need to edit our files all at the exact same time. I do not share them. Thus only 1 person can open and be able to edit it at any time. Others can (at that time) VIEW in read-only mode (excel tells them it is already being used).

    Many of the files are used primarily as "read-only" by the majority of people, with only a couple editing and maintaining the data so this works well. If you require that multiple users MUST edit simultaneously, you are probably going to have problems with excel sharing.

    Steve

  6. #6
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook Tips (2000)

    Hi,

    Just to add my two-bits worth...

    I agree only partly with the suggestion to not share workbooks.

    I've been using Excel now for a few years.
    And only last month, I ( finally dared to) setup a shared workbook for a couple of users who had to enter info into a database !!!

    It was a fairly simple requirement, but even that gave a few unexpected errors !

    As a workaround, I wrote a few simple macros to share, unshare & 'refresh' the workbook.

    The downside of this was that the users would need to keep each other informed if they were sharing or unsharing the workbook.

    I'm not surprised that you found VERY LITTLE even on the Microsoft web-site :-|

    The VBA code is reproduced below if you want to use it.
    It's quick & dirty --

    <pre>Option Explicit


    Sub ShareThisFile()
    With Application
    .DisplayAlerts = False
    With ActiveWorkbook
    .KeepChangeHistory = True
    .SaveAs Filename:=ActiveWorkbook.FullName, _
    AccessMode:=xlShared
    End With
    .DisplayAlerts = True
    End With
    End Sub


    Sub UnShareThisFile()
    With Application
    .DisplayAlerts = False
    On Error GoTo CouldNotUnShare
    ActiveWorkbook.ExclusiveAccess
    .DisplayAlerts = True
    End With

    Exit Sub
    CouldNotUnShare:
    MsgBox "Could not unshare the workbook", vbCritical
    End Sub



    Sub RefreshChanges()
    ActiveWorkbook.Save
    Workbooks.Open Filename:=ActiveWorkbook.FullName
    End Sub</pre>




    I have created an 'Interface' worksheet with 3 buttons which run each of these macros.

    Hope this helps...

Posting Permissions

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