Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UNCs changing (Excel 2000 SR1)

    Hi,

    I have two users - both Windows 2000 - both Excel 2000 SR1 users.

    User A creates a spreadsheet - with some links to external spreadsheets. An example of the syntax used to perform a calculation in one of those cells that ref's an external cell is .... =IF(C21=0,"",C21/'serverdeptfoldersubfolder1subfolder2subfolder3[AW03 wk44.XLS]USC'!C$21-1).

    This spreadsheet can then opened by user A, user B or any other user. All users then have a choice of updating links or not. Irrespective of whether they do or not - the cells all reference the external spreadsheets satisfactorly.

    Now - this is where the prob starts!

    If User B then opens the spreadsheet - and changes a cell - any cell - and then if user A tries to open the spreadsheet - the any of the cells that reference an external spreadsheet change to looking in some default local folder. Looking at exactly the same example as above - the new cell's contents are :

    =IF(C21=0,"",C21/'Cocuments and SettingsusernameApplication DataMicrosoftExcelXLSTARTsubfolder2subfolder3[AW03 wk44.XLS]USC'!C$21-1)

    i.e. it tries to find the file in some sort of local folder.

    I've tried reversing this - ie User A making the change - and this does not have the same effect.

    Anyone got any ideas?

    Thanks in advance.

    amcs

  2. #2
    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: UNCs changing (Excel 2000 SR1)

    I think this occurs if

    "AW03 wk44.XLS" is not found by excel for User B. Note excel will many times "convert the UNC" to the mapped drive("H:" or whatever), which might be different for both people, which can sometimes cause this problem.

    or if user B opens the linked folder "AW03 wk44.XLS" while the "linking file" is also open and saves the linked file on the local drive and then saves the linking file.

    Are either of these occuring?
    Steve

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UNCs changing (Excel 2000 SR1)

    Steve,

    AW03 wk44.XLS can be found by User B - if I go Start, Run and type in serverdeptfoldersubfolder1subfolder2subfolder3AW03 wk44.XLS then they are able to open the spredsheet.


    I have also checked and confirmed that file AW03 wk44.XLS is not open on User Bs machine at the time of opening.

    Any more help??

  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: UNCs changing (Excel 2000 SR1)

    Does this MS article, provide any answers?

    It seems that opening the files via explorer or recently used might screw around with it. It sounds like the file should be absolutely referenced, but perhaps it is not.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UNCs changing (Excel 2000 SR1)

    True, UNC's should be easily and readily useable in Excel without having to worry about having it 'help' by translating them to drive paths, and especially for a supposedly 'web-friendly' application. However, since there are only two users, it might be easier to have both of them map the UNC to the same drive letter and avoid the hassle. Just pick a drive letter that is unlikely to be used in any existing network mappings or hardware configurations, and then it would make adding new users easier as well. I think this might be one of the situations where it is easier to modify your design than trying to force Excel to not do what isn't desired.

Posting Permissions

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