Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    workbook links updating strange behaviour - Excel 2010 SP1

    Several workbooks each with many worksheets (50+)
    but lets just say workbook1 and workbook2

    Workbook1 has multitude of links to workbook2
    Some of the links used a mapped drive references suchs as H:\workbook2.xlsx!$A$3
    Other links (to the SAME workbook) have the path as UNc such as \\ab-norm-1\common\alan\workbook2.xlsx!$B$12

    If the suer has workbook1 open and then opens the workbook2, then the first link reduces the drive reference to just the workbook but the second link still has the UNC full path and does not update.

    Using edit links reveals that we are looking for 2 separate linked workbook2 instances.

    Much strange

    When the workbook2 is being worked on, then using update values in workbook1 will only reveal the last saved value of workbook2 (in other workbooks it updates to the current value even if workbook2 has not been saved)

    Saving workbook2 and then updating the values in workbook1 does reveal the new workbook2 values.



    Has anyone else experienced this and is their any kind of recommended procedure for going about getting the data correct?

    We have used replace to correct the unc path but it seems random as to whether the unc path is used or not. (random shouldn't figure in this..but it feels like it)

    Thanks

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Have you checked that each User has the same drive mappings for the particular PC you are running on???

    If you have workbooks with 50+ worksheets it sounds like the files could be large. So another thing you can do is use Excel's binary file format .xlsb rather than .xlsx or .xlsm
    The binary file format is much more compact, meaning smaller file sizes therefore faster retrieval and less network traffic.
    The binary file .xlsb format also allows macros to be included in the file too.


    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks, been a long busy few weeks so just back on this.
    Yep, all users have same mappings at logon and having checked their sessions the mappings are remaining in place.
    I have tried the files in a controlled area and have found:
    File1 - links to file2 using the mapped drive definition, e.g. M:\alan\file2.xlsx
    File1 also links to file2 using the unc of \\manc\common\alan\file2.xlsx

    With file2 open I changed a couple of values
    Did not save file2
    changed view to File1
    the cells which referred to the mapped drive have updated immediately
    the cells which referred to the unc location have not updated at all
    I did not select to update links, just switched views to the File1

    In File1, I now selected Dat>Edit Links
    Both paths are indicated as containing links to external files.
    I select the mapped drive link and select update (I know the data has already appeared in my file, but I selected it anyway)

    I select the unc link and select update - and although the link status says OK, the data on the worksheet is not updated (I guess until I SAVE the file2)

    Strange goings on, and pretty disconcerting if multiple users are using the files.

    Anyone else have similar experience?
    TIA
    Alan

Posting Permissions

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