Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing link sources for inserted Excel objects (2003)

    We have a MS Word file in which a number (over 20) of different portions of an Excel spreadsheet have been inserted as objects (and linked to the original spreadsheet file). Changes to either file can be made by 2-3 persons but the files are ultimately to be sent to one of these individuals who will disseminate the final updated document. However, as changes are (as a practice) always saved to a different file name, the last individual will frequently receive a new spreadsheet file to which the Word file has to be re-linked. This is done through the Edit, Links, Change Source route in the Word file. However, since there are so many links to the spreadsheet, Word requires the new Excel file to be selected as many times as links, which becomes a quite tedious exercise. This is especially when the links have to be changed under time constraints. (For certain reasons, it is also not possible for a complete set of linked files to be sent to the final individual).

    Is there any way the change can be applied just once and have it take effect for all the linked objects? (Since all the links in the Word file refer to only one Excel file, there is no issue of link confusion).

    Shall appreciate your help.

    Andy

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

    Re: Changing link sources for inserted Excel objects (2003)

    I think it would be much less work to rename the Excel workbook than to adjust all the links in the Word document.

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing link sources for inserted Excel objects (2003)

    Thanks for the reply, Hans.

    I agree with your suggestion - however, as the file names incorporate the date and time of updates and are essential in keeping track of the various versions (of which there can be several during a day), we are having to go this route. Any other suggestions?

    Regards,
    Andy

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

    Re: Changing link sources for inserted Excel objects (2003)

    You could keep the individual versions of the Excel workbook with date and time information in the name, but create a copy with a fixed name of the latest version each time you need it.

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Changing link sources for inserted Excel objects (2003)

    You can replace all the links with a search and replace if you first reveal the field codes.

    If you select all then press Shift F9 you reveal the field codes.
    Then do a search and replace to update the filename.
    Then select all and press F9 to update the fields
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing link sources for inserted Excel objects (2003)

    Just to say a big <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> as I had exactly this problem, but the document has over 200 links.
    I tried using Change Source but it seems that each link has individually to be amended - it doesn't seem possible to select all the links and then change source for all of them.
    Just in case someone else uses this posting, I fell over changing the source for charts; the link for a block of cells contains the full hierarchic name of the file (obviously), then the worksheet name then the range of cells. A chart link has the full hierarchic name of the file, then the worksheet name but the chart identifier uses the terminal filename again.
    After I did the global replace to change the hierarchic file name, all the data table links worked but all the chart links failed - because I had forgotten to globall replace the filename part of the chart link. This took a little while to understand as, having corrupted the chart link, when I tried to see the code, it would only show Error - Invalid Link!
    This posting saved me literally hours of work (the same document framework is used to produce three, separate, audit reports), so I do mean hours.
    Thanks again
    Silverback
    Silverback

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

    Re: Changing link sources for inserted Excel objects (2003)

    Hi Silverback,

    You might like to try my Field Link Updater. See <post#=261488>post 261488</post#>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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