vba code for powerpoint

2
votes

I have an Excel sheet for sales KPIs that is linked to a SQL database and set to refresh every 5 minutes.

I built a PowerPoint that displays in our sales office. I copied the pie charts from Excel, using the Link Data option. When I first build the PowerPoint, this works awesome. Every 5 minutes, when the Excel data updates from SQL, the PowerPoint automatically updates.

However, when I close Excel and PowerPoint and open it the next day, the pie charts in PowerPoint no longer maintain a dynamic link. In other words, I have to manually click on every chart (there are over 15 of them) and click Refresh Data. Even after I do that, the link is no longer dynamic -- it won't auto-update when the Excel chart changes.

To be clear, I have both documents open when this is happening.

I'm assuming there's a way to do this with VBA and would like help to write the code. Once PP is open, I want it to continuously auto-update every time Excel changes. It's okay if Excel has to be open to make this work.

Appreciate any help.

1
vbaexcelpowerpoint
I have not tried any VBA coding yet. Full disclosure, I have not written VBA code before, so I'm looking to see if this is possible to do, and if so, what code would be needed to do so. What I have already done is listed above -- just linking the data when pasted via the Link Data option in paste special. This is PowerPoint and Excel 2013 on Windows 8.1. Thanks. - Mark Holman
I found an article "Insert a linked Excel chart in PowerPoint 2010" in powerpoint help (sorry -- can't figure out how to paste a link). The article says "When you want to update the data in the PowerPoint file, select the chart, and then under Chart Tools, on the Design tab, in the Data group, click Refresh Data. For more information about how to edit chart data, see Edit data in a chart." I don't see auto-update as a feature, and can't explain why you initially observed auto-updating. It may be possible to write VB code to periodically refresh the chart. - xidgel
@xidgel you can paste your links if you answer to this question instead of commenting. look for the Your Answer box below. - Cilvic

1 Answers

0
votes

This is untested, but the idea is that whenever the worksheet is updated (changed) then this should update the links in the powerpoint file called KPIs.pptx.
It may need to pause the macro until the PPTX file is updated, then release the object.

 Private Sub Worksheet_Change(ByVal Target as Range) 
    Dim objPPT as Object
    objPPT = GetObject("C:\username\KPIs.pptx")
    objPPT.UpdateLinks
           'may need a pause here
    onjPPT = Nothing
 End Sub