Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    'Update links?' message on opening (xl97/WinNt4)

    Is it possible to stop this message coming up and have the spreadsheet automatically update without asking on opening?!
    Beryl M


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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    The Workbooks.Open method has an UpdateLinks argument. Possible values are

    <table border=1><td align=center>Value</td><td align=center>Meaning</td><td align=right>0</td><td>Doesn't update any references</td><td align=right>1</td><td>Updates external references but not remote references</td><td align=right>2</td><td>Updates remote references but not external references</td><td align=right>3</td><td>Updates both remote and external references</td></table>
    So

    Set wbk = Workbooks.Open(FileName:="MyFile.xls", UpdateLinks:=3)

    will open the workbook MyFile.xls and update links without asking.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: 'Update links?' message on opening (xl97/WinNt4)

    Can you set this within the sub workbooks_open, ie as the workbook is opened, or does it have to be specified before that? Only the workbook is going to be opened by clicking on a hyperlink ...

    Which reminds me - we've just upgraded to IE6, and previously if we wanted to run macros on opening a document/template/workbook we had to create a .lnk file to do it, because clicking on a hyperlink would open the document etc but would prevent any subs etc from running. Now we've upgraded to IE6 even that won't work any more. How does one set up a hyperlink to open a document and let the subs run?

    I can understand MS wanting to prevent malicious code on the internet by making it so they don't run normally, but surely we must be able to have them run within our own environment and on our own intranet if we want to? Otherwise, this is a bit draconian, even for them!
    Beryl M


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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    I can't remember if the option shown below was available in Excel 97. Select Edit | Links..., then click Startup Prompt...

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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    I've been using IE6 for several years. If I click a hyperlink to a workbook with macros, I get the usual macro warning. If I click "Enable macros", automatic macros such as Workbook_Open are run...

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: 'Update links?' message on opening (xl97/WinNt4)

    Good try, but no, it's not in XL97! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: 'Update links?' message on opening (xl97/WinNt4)

    Hmm - I've just tried it and you're right, XL macros do run - but Word ones don't! Do you havehe same?
    Beryl M


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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    On my PC, the behavior for Word documents is the same as that of Excel workbooks: if I follow a hyperlink to a Word document and click "Enable macros" in the macro warning dialog, the Document_Open event procedure runs.

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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    Laurent Longre describes a registry hack to make links update automatically, but it is a user-level setting, not a workbook-level setting, which may not be convenient. See this nrewsgroup thread.

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: 'Update links?' message on opening (xl97/WinNt4)

    Next question - how do you tell the chart to update its links? I can't find any property for the activechart to update anything, or a reference to links, or ...!

    Not having a good week! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    That's because UpdateLink is a method of the Workbook object, not of individual worksheets or charts.

    ActiveWorkbook.UpdateLink will refresh all links.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: 'Update links?' message on opening (xl97/WinNt4)

    That explains a lot! But when I did that it said

    "Error 1004: Method 'UpdateLink' of object '_Workbook' failed"!!

    and it does so whether I have "ActiveWorkbook.UpdateLink" or "Workbooks("name.xls").UpdateLink"

    What am I doing wrong now?!
    Beryl M


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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    Does this work?

    Activeworkbook.UpdateLink ActiveWorkbook.LinkSources

    (replace ActiveWorkbook with a specific workbook object if you're not updating the active workbook)

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: 'Update links?' message on opening (xl97/WinNt4)

    Nope. Exactly the same error.

    What sort of situations would cause that error? So I can try to work out what it is about my spreadsheet that's causing it?

    I'm pretty sure it is something like that because under Edit > Links 'Update links' is greyed out, but I've no idea why!
    Beryl M


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

    Re: 'Update links?' message on opening (xl97/WinNt4)

    If Edit | Links is greyed out, your workbook contains no external links, so there is no point in trying to update links.

Page 1 of 3 123 LastLast

Posting Permissions

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