Manipulating embeded exccel in word (Repost)

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

Hi there,

I have seen quite a few posts on this and know it is "easily"
accomplished, however I have a bit of a different requirement.

I have a Word document that I have embedded the following:

- 1 Sheet that holds score data that users can edit within word

- 6 Graphs (from the same workbook) that report the data in different
ways.

What I want to happen is that when the data in the embedded worksheet
is edited, then all the other graphs update to reflect the edited data
or scores.

Any ideas ? I have one, which is to cycle through each embedded chart
and copy the data scores from the embedded sheet to the relevant sheet
that the chart is linked to - but is there another way ?

Also as an aside - Is there a way to rename the objects ? Rather than
the arbitarily named 'ActiveDocument.Shapes("Object 3").Select' This
is a problem if you ever have to delete an then add embedded objects

Thanks in advance

Jason
 
JD,

If you've really embedded the workbook, then any changes to a sheet should
be reflected in sheets with dependent formulas. This includes charts.
However, if you've embedded each sheet (worksheet or chart sheet)
separately, they don't know about each other. You should embed once. That
really embeds the entire workbook. Now when you edit a sheet, the chart
sheets should update. You must switch between the sheet and the chart
sheets in the one object. If you're using Insert - Object in Word, do this
once, then while in edit mode for that embedded workbook, add your charts.
If you're using Paste - Special, copying from Excel, then do this once also.
Same deal.

Now if you want the sheet and the charts to appear in separate places in the
Word document, it will require linking (not just embedding) to a common
document. Post back.
 
Thanks Earl

That makes sense, and seems to work when I manualy copy a chart from
the Embedded workbook and then Embed it as a linked, in another part of
the Word document.

[To summarise - I am embedding a linked chart from an already embedded
Excel worksheet that contains the data (and charts)]

Word
|
|__>Embedded Excel Workbook with data sheet and several charts
|
|____>Embedded, Linked Charts from above.

Now I just need to work out how to update all this automatically - the
update link doesn;t seem to "referesh" the charts.

I assume if I embed/link all my charts then these should automatically
update when the Embedded Source data sheet is changed. I am having a
few problems with this, but I will keep plugging away.

If you have any tips or hints on this let me know.

Thanks

Jason.
 
JD,

Have the chart in a worksheet (even if it's by itself), not a chart sheet.
Paste the workbook into the Word document as an Excel workbook object.
Double-click it into edit mode.
Navigate to the chart, select it. Copy.
Navigate back to the sheet. Click outside the edit box to leave edit mode.

Click to the location in the Word document that's to have the linked chart
Edit - Paste special - Microsoft Excel Chart Object, Paste Link.

Done.
 
Thanks Earl. That is pretty much what I ahve tried - except I have the
cahrs as their own sheet. I will try your suggestion and just place
the charts within a worksheet and hopefully that will fix the quirks.

Thanks again.

Jason.
 
Back
Top