CODEWITHSUNDEEP
vbachartsms-word
Jander Friebe
Jander Friebe

Reputation: 23

VBA Word: Change Data of charts

I want to change the data of a chart in a Word Document, but I can't find the right way to address my charts. I tried several techniques, but nothing worked. (I´d love to open a ExcelSheet in which I can just change the Data)

So to put it all together: I want to change the data (not the source), of a MS Word chart, which looks like that:

Screenshot of an old chart

Edit(13.8.): After request, I try to give you some "reference Code" to work with. 

Sub ChangeChart()
Dim aktDocument As Document
Dim chrt As Chart
Dim SourceSheet As Excel.Worksheet

Set aktDocument = ActiveDocument    
Set SourceSheet = aktDocument.Shapes(1).Chart.OpenSourceData 'I know it´s not that easy

SourceSheet.Range("B5") = newStuff

aktDocument.Shapes(1).Chart.SetSourceData = SourceSheet

End Sub

I know this may sounds utopic and ridiculous, but I just don´t know, how to address the chart in the right way, or to even work with it properly.

Edit(15.08):

Even after recreating the old charts, the following code is not able to find a shape which has a chart. And therefore it stops when the index is out of range. 

Sub Test()
  i = 0
  Do While i < 100
    i = i + 1
      If ActiveDocument.Shapes(i).HasChart Then
         MsgBox "found one!"
      End If
  Loop
End Sub

Solution(30.08.):

The answer from @Cindy Meister was the solution to my problem. After further working with it, I came to the problem, that the ChartData always opens on the screen, while running the code.

Just for reference this question was my workaround.

Upvotes: 0

Views: 4091

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

All Office applications use the Excel engine to create and manage charts. In Word, charts can be formatted in-line with the text or with text wrap formatting. In the former case, a chart object needs to be addressed via the InlineShapes collection, in the latter via the Shapes collection.

Since your sample code uses Shapes(1) I've used that in the code snippet below. If it's not certain that the first Shape in the document is the chart, but you've assigned the Shape a name, you can use that as the index value (for example Shapes("MyChart"). Or you can loop the Shapes collection and check HasChart.

HasChart returns True if the Shape (or InlineShape) is a Chart. It's then possible to set Shape.Chart to an object variable. The chart's data can be accessed using Chart.ChartData.Activate - if you don't use Activate it's not possible to access the data when the chart's worksheet is stored in the Word document. Only then can Chart.ChartData.Workbook return a workbook object, and through that the worksheet can be accessed using ActiveSheet. From that point on, it's like working with the Excel object model.

Sub ChangeChart()
    Dim aktDocument As Document
    Dim shp As Word.Shape
    Dim chrt As Word.Chart
    Dim wb As Excel.Workbook, SourceSheet As Excel.Worksheet

    Set aktDocument = ActiveDocument
    Set shp = aktDocument.Shapes(1)
    If shp.HasChart Then
        Set chrt = shp.Chart
        chrt.ChartData.Activate
        Set wb = chrt.ChartData.Workbook
        Set SourceSheet = wb.ActiveSheet
        SourceSheet.Range("B5").Value2 = newData
    End If
End Sub

Upvotes: 2

Related Questions