Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links (file not found dialog) (2000)

    Hi all,

    In a workbook consisting mainly of links to another workbook, is there any way prevent Excel from seeking to update the links? I.e., if I send the workbook to a third party who does not have the workbook containing the link sources, Excel prompts the user to manually find the source book. I wish to send the book to a third party essentially with the information only without copying the sheets data only or by making a screen capture. Any ideas?

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links (file not found dialog) (2000)

    Jeepers! I checked the path of the workbook on open and set .UpdateRemoteReferences=False if the file was not in the proper place and it worked! Thanks for your support! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Links (file not found dialog) (2000)

    Hi Mike,
    I think your solution might fail if the user elects not to allow the macro to run when the workbook opens. In that case, the updating behaviour is controlled by Tools|Options|Calculation|Update Remote references and, even when that's not set, the user might still fall over it via Edit|Links|Update. Ultimately, they'd probably be best off with a copy with the links removed.
    Cheers?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links (file not found dialog) (2000)

    Me thinks that you are correct! The UpdateRemoteReferences idea I had so gleefully rejoiced in does not work either. I emailed it to a store today and when I opened it, still got the "Look for file dialog".

    Even stored the values in a temp string, cleared the formulas and restored the values in string format to the cells that had formulas. Works great, except that the update links process takes place before the open event, therefore I get the "Look for dialog" THEN the links get replaced with the previous cell contents. Any ideas how to control the link update process based on where the file is located?

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Links (file not found dialog) (2000)

    Other telling the people where the file is located to go into Tools|Options|Calculation and unchecking 'Update remote references', no. If you do go down that path, it's not specific to a given workbook or Excel session.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Links (file not found dialog) (2000)

    Only really reliable method: Remove links, save and *then* send it to others.
    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
  •