Change an excel chart with word-vba

[IkEcht]

Hi,

I try to change the values of two (out of seven) variables in my excel-chart, thus altering the chart, before copying the chart to my word-document. But I can't seem to alter the document the way I want to. Can someone tell me what I'm doing wrong and point me in the right direction?


Sub grafiekdichtheid()

Dim i As Integer
Dim gevonden As Boolean
Dim xl2 As Object
Dim jaardichtheid As Integer
Dim dichtheidfb1 As Integer
Dim dichtheidfb2 As Integer
Dim inwfb2 As Long
Dim inwfb1 As Integer
Dim jaarfb1 As Integer

jaardichtheid = xl.Sheets("Bronnen").Range("j480")
dichtheidfb1 = xl.Sheets("Bronnen").Range("j478")
dichtheidfb2 = xl.Sheets("Bronnen").Range("j479")
inwfb2 = xl.Sheets("Bronnen").Range("j481")
inwfb1 = xl.Sheets("Bronnen").Range("c2")


Set xl2 = CreateObject("Excel.Application")
xl2.Workbooks.Open ("q:\Fietsbalans-2 uitvoering\Rapportage\stedelijke dichtheid fietsbalans-2 rapportage v2.xls")

Do
For i = 2 To 219
If xl2.Worksheets("stedelijke dichtheid fietsbalan").Cells(i, 2) = gemeente Then
xl2.Worksheets("stedelijke dichtheid fietsbalan").Range("p" & i) = dichtheidfb1
xl2.Worksheets("stedelijke dichtheid fietsbalan").Range("q" & i) = dichtheidfb2
xl2.Worksheets("stedelijke dichtheid fietsbalan").Range("r" & i) = inwfb1
xl2.Worksheets("stedelijke dichtheid fietsbalan").Range("s" & i) = inwfb2
xl2.Worksheets("stedelijke dichtheid fietsbalan").Range("v1") = gemeente
xl2.Worksheets("stedelijke dichtheid fietsbalan").Range("r1") = jaarfb1
xl2.Worksheets("stedelijke dichtheid fietsbalan").Range("s1") = jaardichtheid
gevonden = True
i = 219
End If
Next i
Loop Until gevonden = True Or i = 220

With xl2.Sheets("grafiek1")
.SeriesCollection(6).XValues = "='stedelijke dichtheid fietsbalan'!R" & i & "C18"
.SeriesCollection(6).Values = "='stedelijke dichtheid fietsbalan'!R" & i & "C16"
.SeriesCollection(7).XValues = "='stedelijke dichtheid fietsbalan'!R" & i & "C19"
.SeriesCollection(7).Values = "='stedelijke dichtheid fietsbalan'!R" & i & "C17"
End With

xl2.Sheets("grafiek1").Select
xl2.ActiveChart.ChartArea.Select
xl2.Selection.Copy

Selection.PasteSpecial link:=False, DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False
xl2.ActiveWorkbook.Close savechanges:=False
End Sub

[Dave]

I don't follow... is it a chart problem or a Word problem? Maybe a bit more info is needed. Dave
ps. Is that German?

[IkEcht]

Not really sure myself. I would say a chart problem that arises in word. As the code works in excel. But I want to change the chart from within word, as the chart is pasted into a document immediatly and not needed for any other reason.

ps. no it's Dutch

[Dave]

I'm pretty sure that you can't change the chart from within Word. You need to change the chart in XL before it is pasted to the document. HTH. Dave