Modify the range of a embedded chart in PowerPoint

Question

I want to change the range of my embedded chart in PowerPoint on Slide 2 (data in excel) from the Range (C2:F2) to the Range (C3:F3) and update the chart automatically.

Option Explicit

Sub ModifyChartData()

 Dim WB As Workbook

Set WB = Workbooks.Open(FileName:="U:\Automatisierung\Auto.xlsx", ReadOnly:=True)

With ActivePresentation.Slides(2).Shapes("Chart 1").Chart.ChartData
    .Activate
    WB.Sheets("Chart").Range("B2").Value = WB.Sheets(2).Range("B3").Value
    WB.Sheets("Chart").Range("C2").Value = WB.Sheets(2).Range("C3").Value
    WB.Sheets("Chart").Range("D2").Value = WB.Sheets(2).Range("D3").Value
    WB.Sheets("Chart").Range("F2").Value = WB.Sheets(2).Range("F3").Value
    WB.Close SaveChanges:=True
End With




End Sub

EDIT: I have updated the code and the value are now being changed from B2 --> B3 etc. I have now the problem with the workbook: I want that the chart is updated and the workbook closes again. For: WB.Close SaveChanges:=True --> It wants to save the file a new. WB.Close SaveChanges:=False --> I lose the updated chart.

How can I save and update the file within the macro?

Thanks a lot!

See this question. Not sure why you're opening a workbook. Are you working from Powerpoint or Excel? — BigBen
@BigBen the given question changes the values but I just want to move the Range and update the chart in PowerPoint. — Bartek
Please edit your question with the code you tried and the error you're getting, so we can help you, thanks! — BigBen
As BigBen has pointed out, your code opens a workbook from drive U, not a workbook embedded in PowerPoint. So the With ActivePresentation line is doing nothing. You can open the workbook from drive U if the chart is linked to PowerPoint, not embedded. If you want to work with a chart embedded in PowerPoint, you need to open the embedded object, not a workbook on your disk. — John Korchok

Bartek Bartek · Accepted Answer · 2019-07-23T07:49:35

Thanks to @BigBen and @John Korchok I managed to solve this problem with the following code:

    Option Explicit

Sub ModifyChartData()


With ActivePresentation.Slides(2).Shapes("Chart 1").Chart.ChartData
    .Activate
    .Workbook.Sheets(2).Range("B2").Value = .Workbook.Sheets(2).Range("B4").Value
    .Workbook.Sheets(2).Range("C2").Value = .Workbook.Sheets(2).Range("C4").Value
    .Workbook.Sheets(2).Range("D2").Value = .Workbook.Sheets(2).Range("D4").Value
    .Workbook.Sheets(2).Range("F2").Value = .Workbook.Sheets(2).Range("F4").Value
    .Workbook.Close SaveChanges:=True
End With




End Sub

Modify the range of a embedded chart in PowerPoint

1 Answers