Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically udpate links (2003)

    Hello all

    I have several workbooks ("models") which share inputs ("input") contained in another workbook. All of these are being developed by different people and will be emailed around etc. I am trying to link all the models to the input, so that the models update the linked cells automatically when opened (when the input is not open).

    I tried using Edit > Links > Startup Prompt > Don't display the alert and update the links

    But this does not seem to work - when I open any of the model workbooks, I get prompted with the standard dialog box asking me if I want to update / don't update / help! This is the same as when the startup prompt was "let the user decide" - which I don't want to allow.

    The questions I was hoping someone would be able to help with are:

    1) Can I do something differently to ensure links are automatically updated on startup - without prompting the user?

    2) Can I replicate this functionality in a macro somehow? The problem is these files are going to be emailed around, so I cannot use the absolute path in the link (i.e., I can't rely on "C:folderfile2" - I want to use relative paths i.e., "file2").

    3) Is there another solution to ensure all models automatically update the values of inputs from the input workbook? (that does not rely on absolute paths)

    Thanks in advance

    Salil

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically udpate links (2003)

    Welcome to Woody's Lounge!

    Make sure that "Ask to update external links" in the Edit tab of Tools | Options... is *not* ticked. This is, however, a user-level setting, not a workbook-level setting.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically udpate links (2003)

    Further to Hans' response the attached files provide one possible approach.
    <UL><LI>Book1.xls is the model workbook containing the links which must be updated. It is password protected.
    <LI>Open_Book1.xls is used to control the option setting "Ask to update automatic links".
    <LI>Both files must be saved in the same folder.
    <LI>The code is stored in the ThisWorkbook module of Open_Book1
    <LI>The Target constant must be set to the name of the model workbook.
    <LI>The password for the model workbook must be provided in the "Workbooks.Open" command; in this case "mypwd".[/list]H.T.H.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    New Lounger
    Join Date
    Feb 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically udpate links (2003)

    Thanks for your help guys.

    I didn't want to use the user level setting because I want to email these models around to several people.

    Don's example was great - avoided the prompt. I couldn't use it as is, because there are several models and I don't want to have a separate "open model" workbook for each of them - will multiply the number of files.

    I modified Don's code - on opening the model workbook I now force an open and close of the input workbook which forces all links to refresh. The only problem is that I'm still prompted with the Update / Don't Update dialog. But I guess I can live with that.
    Attached Files Attached Files

Posting Permissions

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