Update existing embedded chart in powerpoint using excel vba

Question

I have an inserted chart in powerpoint. I am using this as a template. I would want to edit the data of this chart with data from an excel sheet. Is there an excel vba code for this

Answer 1

Any Chart in PowerPoint (there are exceptions, and it is possible to "break" existing charts, but that's not in scope, here) has a ChartData property, which returns an Excel Workbook that contains the data for the chart.

When working from a "template" slide, it should be safe to assume that the chart data exists on Sheet 1, and in a ListObject table (there should be only one such table in the sheet).

In PowerPoint VBA, requiring reference to Excel object library, this shows you how to get a handle on the ListObject which contains the chart's data:

Sub ShowChartData()
Dim sld As Slide
Dim shp As Shape
Dim cht As Chart
Dim chtData As ChartData
Dim cTable As Excel.ListObject

'Assume we have only one slide, at slide 1:
Set sld = ActivePresentation.Slides(1)
'Assume the Chart is the second shape, modify if needed
Set shp = sld.Shapes(2)    
'Handle the chart
Set cht = shp.Chart
'Handle the CharttData
Set chtData = cht.ChartData

'Open & minimize the ChartData, you don't need to see it, but it must be OPEN to edit it
chtData.Activate
chtData.Workbook.Application.WindowState = -4140

With chtData
    Set cTable = chtData.Workbook.Worksheets(1).ListObjects(1)

    ' Here, you can update the ListObject in the same ways you
    ' would do so in Excel, natively.

End With

'Remember to close the workbook
chtData.Workbook.Close

End Sub

Now that you have a handle on the ListObject, you need to somehow get the values from Excel.

This will require handling an open instance of Excel.Application class (or prompting the user to select a file from a FileDialog, etc.) and identifying which data to put in the PowerPoint, and how to arrange it. Usually this can be done by dumping the values from Excel in to a variant array, and passing that to PowerPoint.

Since those are all details you've omitted, please note that I am absolutely not willing to entertain what is likely to be a never-ending series of "but how do I do such-and-such...?" follow-up questions as you suss out the complexities of your own logic and use-case requirements.

The above code is designed to execute from PowerPoint. If you need to run it from Excel, it will require different code (untested, but something like this).

Sub ShowPPTChartData()
' to be run from Excel VBA
'Requires reference to PowerPoint library
Dim ppt as PowerPoint.Application
Dim pres as PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim cht As PowerPoint.Chart
Dim chtData As PowerPoint.ChartData
Dim cTable As Excel.ListObject

Set ppt = GetObject(,"PowerPoint.Application")
'Assume we have only one open Presentation file:
Set pres = ppt.Presentations(1)
'Assume we have only one slide, at slide 1:
Set sld = pres.Slides(1)
'Assume the Chart is the second shape, modify if needed
Set shp = sld.Shapes(2)    
'Handle the chart
Set cht = shp.Chart
'Handle the CharttData
Set chtData = cht.ChartData

'Open & minimize the ChartData, you don't need to see it, but it must be OPEN to edit it
chtData.Activate
chtData.Workbook.Application.WindowState = -4140

With chtData
    Set cTable = chtData.Workbook.Worksheets(1).ListObjects(1)

    ' Here, you can update the ListObject in the same ways you
    ' would do so in Excel, natively.

End With

'Remember to close the workbook
chtData.Workbook.Close

End Sub

EDIT It is possible to edit an existing chart without Activating the ChartData.Workbook as can be demonstrated here:

Update PowerPoint chart without opening chart workbook or making it invisible

Adding/removing series from the charts is trickier than manipulating data that's already part of the chart series, however.

Answer 2

Set Current_Chart_Shape = Active_Slide.Shapes("Monthly Chart")
Set Current_Chart = Current_Chart_Shape.Chart
Set Current_Chart_Data = Current_Chart.ChartData

Current_Chart_Data.Activate
Set PPTChartSheet = Current_Chart.ChartData.Workbook.Sheets(1)
Current_Chart_Data.Workbook.Application.WindowState = -4140

With PPTChartSheet
.ListObjects("Table1").Resize PPTChartSheet.Range("A1:C8")
For l = 0 To 6
.Range("B2").Offset(l, 0).Value = Array_Values(l)
Next l
End With

Current_Chart_Data.Workbook.Close

Answer 3

You don't need to do anything special.

Once a chart is embedded and linked to an Excel sheet, PowerPoint couldn't care less when or how it's updated, even when the PowerPoint file is closed.
Basically, whatever you do to that sheet, VB or manually, will be reflected in the PowerPoint next time you open it.