Change linked excel file via VBA in PowerPoint

Question

I have multiple slides with multiple charts having sample data. I want to update these charts with actual data which is present in excel files located in the same folder. Can I do this via VBA inside ppt and how?

I read this answer edit chart data in powerpoint but this does not specify where I can mention the excel file and select the specific data.

Please help!

It might be simpler to create your sample charts in Excel and link them to Powerpoint. Then copy the Excel file to a new file, modify it and in PowerPoint, change the linked chart's .LinkFormat.SourceFullName to point to the new file. OR copy the original sample file to a new name, then edit the data in the sample file, the one linked to PPT already. No code required at all. — Steve Rindsberg

nwhaught nwhaught · Accepted Answer · 2019-01-31T13:16:04

On the question you linked to, the top answer provided the following code:

With ActivePresentation.Slides(sl).Shapes(sh).Chart.ChartData
    .Activate
    .Workbook.Sheets(1).Range("A1").Value = "test_data"
    .Workbook.Close
End With

One of the comments on the answer pointed out that once you Activate the ChartData, you're basically dealing with Excel. You can also launch an actual instance of Excel inside the Powerpoint VBA, then just open your workbooks, extract the data from them and put it into your ChartData objects like you would do if you were working in VBA in excel.

In order to launch an instance of Excel in powerpoint VBA try the following:

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

xlApp.Workbooks.Open "C:\lol\Book1.xlsx", True, False

'once Book1 is open you can extract data from Book1 and move it to 
'the appropriate place in .ChartData.Workbook

Set xlApp = Nothing

(code above sourced from this answer)

Change linked excel file via VBA in PowerPoint

1 Answers