Pasting Cells Into Word Chart

[Breather2008]

Hello,

I have a word document that is basically a skeleton document with a persons salary and benfits. When I run my macro in excel it updates all the fields with the persons name, salary and benefits without issue! My only problem is that in the word document I have made a pie chart that has all the columns formatted how I want.

I am now trying to make a macro that will take, lets say, the first four columns of row 1 in my excel document and paste it into the word chart spreadsheet so that the chart changes. Any help would be great! So far the only progress I have made is to activate the datasheet in the word document with the following line of code (but cant figure out how to edit the data!):

WordApp.ActiveDocument.Shapes(4).OLEFormat.Activate

Thank you for any help!!

[mdmackillop]

Hi,
Welcome to VBAX.
Can you santise your files and post them? You can do this by zipping them together and attaching the zip using Manage Attachments in the Go Advanced section.
Regards
MD

[Breather2008]

Thank you for taking a look at this!


:banghead:

[mdmackillop]

I can' see a method of linking Excel to this chart data, so maybe a different approach.
Create your chart in Excel, Copy and PasteSpecial/PasteLink into thw Word document. Changes in your data should update the Word document.

[Breather2008]

Could you direct me to a page that can show me how to do that or if it's simple, show me how to update the chart in Word VBA (I'm assuming thats what you had in mind). Thank you very much!!

[Breather2008]

I think I figured it out. Here is the code incase anyone else runs into this problem:

Private Sub Chart_Updater(WordApp As Word.Application, iRow As Integer)
Dim oGraph As Object
Dim rngNewRange As Excel.Range

Set rngNewRange = ActiveSheet.Range(Cells(iRow, 2), Cells(iRow, 6))

rngNewRange.Select
rngNewRange.Copy

Set oGraph = WordApp.ActiveDocument.Shapes(4).OLEFormat
oGraph.Activate
oGraph.Object.Application.DataSheet.Cells(2, 2).Paste

oGraph.Object.Application.Update
oGraph.Object.Application.Quit

Set oGraph = Nothing
End Sub