This is the approach I would use:
- Set up charts natively in PPT using the Insert Chart.
- Then, from VBA, for each diagram, data is collected from the Excel source file and the data is stored in
array variables. - Use these variables to update the data in a series of charts (alternatively, update the built-in chart sheet Powerpoint
.ChartData ).
There are other methods, such as using OLEObjects for linking / embedding, but frankly, this is a pain for work and can cause problems if files (files) are on a shared drive, if they are moved or renamed, etc.
Here is the general structure described above.
This will require a lot of modification at your end - for example, it is configured for only 1 chart on 1 slide, and I have no idea how your data is organized in Excel, so I just insert some kind of dummy code to show how I captured some values from Excel, you obviously will need to fine-tune it with a good amount of code so that it is dynamic enough to work in all diagrams (this can be done quite easily if your data is well organized and you know your way around Excel VBA).
Option Explicit Option Base 1 Sub GetChartDataFromXLS() Dim wbFileName As String '## full filename & path of the Excel file.' Dim oXL As Object Dim xlWB As Object Dim xlWS As Object Dim cl As Object Dim c As Long Dim shp As Shape Dim cht As Chart Dim srs As Series Dim x As Long Dim sArray() As Variant '## temporary array for each series, will be stored in chtData array.' Dim chtData() As Variant '## I would use this array to store several arrays from the Excel file.' Dim s As Long wbFileName = "C:\users\david_zemens\desktop\dummy chart data.xlsx" Set oXL = CreateObject("Excel.Application") oXL.Visible = True Set xlWB = oXL.Workbooks.Open(wbFileName) '## iterate over the shapes in the slide.' For Each shp In ActivePresentation.Windows(1).Selection.SlideRange(1).Shapes '## check to see if this shape is a chart.' If shp.HasChart Then '## set the chart variable.' Set cht = shp.Chart '## clear out any existing series data in the chart' For s = cht.SeriesCollection.Count To 1 Step -1 Set srs = cht.SeriesCollection(s) srs.Delete Next '##Your code to get the chtData will go in this block:' '## Set xlWS = xlWB.Sheets(1) '
This method does not write to the chart data sheet. Honestly, I see that as an unnecessary step, if you are creating a control panel with macro definition, there should be no reason to need a data sheet, but if this is necessary for some reason, we can change the way we create a chart series.