Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Open Word Doc with Links (2000 sp3)

    Hi all! I have another problem I hope you can help me with. Can I suppress the link update when I open a Word document from Excel? ...at least I think that's the question I need to ask, let me explain...

    I have a Word document that has links to a workbook. Both files are in the same folder. The users will copy this folder to their own computer from a share drive and may place the folder within another folder.

    So, I need to rebuild the links in the Word document so they point to the new workbook and I want to do it from Excel VBA code (long story). I created code in the Word document that did in fact rebuild the path (for charts too) in the links correctly and I tried to convert it so I can run it from Excel. I think I have all that working. Problem is, when the Excel code in the new folder opens the new Word doc, there is this long delay while it opens and closes the old Excel file for each link. Since there are a boat load of links and the Excel workbook is huge, this takes forever. Also, there will be cases where the old workbook will no longer be accessible.

    I don't know if the links are being updated during this process but they sure don't need to be updated at open, just after I change them to point to the new workbook. When I open the workbook manually, it asks if I want to update. Is there a way to answer "NO" to that question in code when I issue the open statement? I don't see anything in the help file as an argument to the open statement nor could I find anything when I searched these forums.

    Any help would be most appreciated. By the way, this is the best forum on the web! You guys are great!

  2. #2
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Word Doc with Links (2000 sp3)

    Never mind!!! This line of code before the open solves the problem:
    objWord.Options.UpdateLinksAtOpen = False

  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: Open Word Doc with Links (2000 sp3)

    Hi VBA-Bumbler,

    Attached is a Word document with code to update its own links (of whatever kind) so that they point to the folder the document is in every time it's opened. This would obviate the need to update the links from Excel - after all, how do you ensure that the code in Excel workbook is run before the document is opened?

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Word Doc with Links (2000 sp3)

    Thanks for the reply. This is certainly a good option and your points are very valid. However in our case, we have a huge amount of links and we want to minimize the time it takes to open the document. We have a folder building process as part of the setup and we made the link rebuilding part of that process.
    By the way, does you method cause the old linked workbook or the new linked workbook to open for each link?
    Thanks

  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: Open Word Doc with Links (2000 sp3)

    Hi VBA-Bumbler,

    I suspect that the time taken to open a document is more a function of Word being set to 'Update automatic links at Open', than to the running of the code to find and replace the links. In fact, my code doesn't execute either a .LinkFormat.Update or .Fields.Update statement, so it wouldn't cost you any time there. You might like to give it a go and see how it performs. The one drawback with this approach that may be an issue for you is that the code replaces all links every time the document is opened, even when the document location hasn't changed. I could easily enough refine it not to do that, though.

    As for whether the method links to a new or old workbook, it links to whatever version is in the document's folder.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open Word Doc with Links (2000 sp3)

    Thanks! I will give it a try. It certainly is a good alternative to my approach and I agree about the Word setting.

Posting Permissions

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