Word/Excel 2007 - copy chart object problems

[Brian Ipsen]

Hi

I'm trying to get a chart object into a Word document (in order to be able
to get the chart values updated when a user changes vaules in a table in the
word document) - but I haven't had luck to get things working so far....

I tried to record some macros in order to see which commands to use - and
have put it together into this piece of code in my word document:

Sub UpdateChart()
'start wour word code here

'--- open a workbook
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
If Err.Number = 429 Then
'Excel is not running; creating a Excel object
Set objXL = CreateObject("Excel.Application")
Err.Clear
End If

'I use a template for my graph
Set objWkb = objXL.Workbooks.Add("c:\temp\data.xltx")

'for debugging this would make it visible
objXL.Visible = True

'select your chart you need to have set your worksheet as an object and in
my case I only have on chart which is (1)
ActiveSheet.Range("B3").Select
ActiveCell.FormulaR1C1 = "60"
ActiveSheet.Range("B4").Select
ActiveCell.FormulaR1C1 = "15"
ActiveSheet.Range("B5").Select
ActiveCell.FormulaR1C1 = "20"
ActiveSheet.Range("B6").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Copy

'go back to word
Set objWord = Application.ActiveDocument

objWord.Activate
With objWord.Selection 'i set a bookmakr called chart
.GoTo What:=wdGoToBookmark, Name:="chart"
.PasteAndFormat (wdPasteDefault)
.PasteSpecial (wdPasteMetafilePicture)
.TypeParagraph
End With

'fix it up for my document
'objWord.ActiveDocument.Shapes("Picture 2").Select
'objWord.Selection.ShapeRange.ConvertToInlineShape

End Sub



First - the values in my excel sheet are not updated as I specify .... Maybe
that is also why the copying of the chart object doesn't work...

Second - Nothing is added to the word document (the cursor isn't jumping to
my bookmark - even though it is defined)....

Can anyone tell me what I'm doing wrong ?



Regards

Brian