CODEWITHSUNDEEP
vbachartspowerpoint
TehDrunkSailor
TehDrunkSailor

Reputation: 780

How do you change the default data of a chart in PowerPoint with VBA?

I would like to create a clustered column chart and add it to a PowerPoint slide. I do not want to create that graph in Excel, and then copy it over using the PowerPoint.Slide.Shapes.PasteSpecial method. I have no problem creating the chart, and interacting with it to a limited extent (for example I can disable the legend, see code). However, I cannot figure out how to edit the data that the chart is referencing.

Private Sub AddGraph()

' Create a new slide
Dim PowerPointApplication As PowerPoint.Application
Set PowerPointApplication = New PowerPoint.Application

Dim Presentation As PowerPoint.Presentation
Set Presentation = PowerPointApplication.Presentations(1)

Dim Slide As PowerPoint.Slide
Set Slide = Presentation.Slides.Add(Presentation.Slides.Count + 1, ppLayoutText)

' Remove the existing shape 2
Slide.Shapes(2).Delete

' Add the chart
Slide.Shapes.AddChart _
    Type:=xlColumnClustered

With Slide.Shapes(2).Chart

    .Legend.Delete

    ' Try to edit some of the default data of the cluster column chart
    ' This causes a Subscript out of Range error 
    WorkBooks("Chart in Microsoft PowerPoint").Cells(2,1) = 2021
    
End With

End Sub

When PowerPoint creates the chart, it seems like it also creates a temporary(?) Excel workbook to store the data in. I tried to access that workbook to edit the default data like this WorkBooks("Chart in Microsoft PowerPoint").Cells(2,1) = 2021 for example, but this throws a Subscript out of range error.

Is there a way to edit this Excel workbook that's created when a chart is created in PowerPoint? Have I gone too far down a fruitless path; is there an easier way to get this done (excluding creating everything in an Excel workbook, and then copying that chart over to a PowerPoint presentation)?

Upvotes: 0

Views: 274

Answers (1)

John Korchok
John Korchok

Reputation: 4913

ChartData is the missing link. Here is typical code to alter the worksheet values:

With Slide.Shapes(2).Chart
    With .ChartData
        .Activate
        strText = .Workbook.Worksheets(1).Range("B2").Value
        strText = Replace(strText, "4.3", "5.2")
        .Workbook.Worksheets(1).Range("B2").Value = strText
        .Workbook.Close
    End With
End With

Upvotes: 1

Related Questions