Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Dec 2002
    Location
    Perth, Western Australia, Australia
    Posts
    730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Managing external links (2000 / XP)

    I am looking for a way to override the automatic addition of the directory path to external links when a workbook is saved.

    The location of the linked workbook (linkEE) is always known to the workbook with the links (linkER). LinkEE maybe in either a a directory specified in a parameter file, or the same directory as linkER.

    The Workbook_Open event of linkER looks for linkEE, and opens and hides it, or alerts the user to its absence (only when needed).

    This works fine while the workbook locations remain static. But when the locations change it all falls apart. Typicallly this happens when a user needs to work at home on copies of workbooks from the work LAN environment, and later return them to the LAN. It would not be an issue if they knew how to change links, but they don't, and won't.

    Although linkER knows where linkEE is, and opens it OK, the links fail because Excel stores the directory path as part of the external link and wants to access linkEE from where it was last time linkER was saved, rather than use the one that is already open.

    Of course, both linkER and linkEE are large and complex, the links are both direct and indirect (via OFFSETs, INDIRECTs, and dynamic named ranges), and the user level is at where they will <img src=/S/flee.gif border=0 alt=flee width=25 height=25> at a hint of "The workbook you opened contains links to... ".

    Since the location of linkEE is known, a possible workaround would be to write code to "refresh" the link directory paths during Workbook_Open. However, I'm reluctant to pursue this path <img src=/S/pun.gif border=0 alt=pun width=22 height=18> due to the varied link types, their number (thousands), and the limited "grunt" of most user PCs. Alternatively, code in the Workbook_BeforeSave event could remove the link directory paths, but is still far from ideal.

    Anyone have a more elegant solution, with less <img src=/S/smash.gif border=0 alt=smash width=30 height=26> ?
    <font face="Comic Sans MS" color="blue">TimOz</font>
    <img src=/S/flags/Finland.gif border=0 alt=Finland width=30 height=18> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing external links (2000 / XP)

    I think that the only way to do this is to always keep the workbooks in the same directory. The only other thing that I could think of would be to write code to find and fix all of the links.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Dec 2002
    Location
    Perth, Western Australia, Australia
    Posts
    730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing external links (2000 / XP)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>I was afraid that might be the answer <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> .

    I'll think about the feasibility of user training <img src=/S/pinkelefant.gif border=0 alt=pinkelefant width=20 height=20> , while waiting a bit longer in case anyone else has an <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15> .

    I know there's a bunch of very <img src=/S/clever.gif border=0 alt=clever width=15 height=15> people around here, in addition to the invaluable search facility.
    <font face="Comic Sans MS" color="blue">TimOz</font>
    <img src=/S/flags/Finland.gif border=0 alt=Finland width=30 height=18> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Managing external links (2000 / XP)

    As a heads-up: it should work when the relative paths stay the same: if the source workbook is always in the same location relative to the location of the "target" workbook, then it should keep working. Provided you haven't used any INDIRECT functions with complete folder information in them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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