Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Elk River, Minnesota, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Source for Chart Objects (Word 2000)

    I have a Word 2000 document with linked charts and tables in Excel. I want to change the source from one spreadsheet to another. This works for the tables but not the charts. Is there a solution to change these without having to delete the oldcharts in my Word document, then copy and paste link new charts from the new spreadsheet? I am linking approximately 90 tables and 90 charts. I am doing this to 30 documents, all about 200 pages.

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Change Source for Chart Objects (Word 2000)

    You should be able to change the link source, without having to delete and reinsert new charts.

    Don't know if it will work exactly the same in 2000, but in Word 2002 you can right-click on the linked chart in the Word document, and from the context menu select Linked Worksheet Object > Links to display a Links dialog.

    In the Links dialog there is a 'Change Source' button that should allow you to point the link to a different XL file.

    This provides a manual means to update the link source in your documents, but if you have 30 documents with 90 charts each, that's still going to be a lot of work.

    This sounds like a situation where a macro could be put to good use, but that may not be possible in this case - when I record a macro doing the above actions, the recorded macro comes out blank, so it's likely that the Word VBA object model doesn't allow you to get at this procedure via code.

    Gary

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

    Re: Change Source for Chart Objects (Word 2000)

    This code snippet from the online help seems to point in the right direction:

    With ActiveDocument.Shapes(1)
    If .Type = msoLinkedOLEObject Then
    With .LinkFormat
    .SourceFullName = "C:My DocumentsmyExcel.xls"
    End With
    End If
    End With

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Change Source for Chart Objects (Word 2000)

    Hans,

    Thanks for this snippet. For charts, had to change it to InlineShape, but still haven't been able to get it to work.
    The code runs but the link doesn't actually update, even with an .Update command added in.
    Will post back if an "aha" occurs.....

    Gary

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

    Re: Change Source for Chart Objects (Word 2000)

    You can do this without a macro by:
    1. Selecting All = Ctrl-A
    2. Reveal all field codes = Shift-F9
    3. View the path that the links use
    4. Do a search and replace to replace the old path with the new path. Note: match the syntax.
    5. Update Fields = F9
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

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