Run an Excel Macro to Update a Powerpoint Linked Chart (Workbook Already Open)

Question

Updated & cross-posted from: http://www.ozgrid.com/forum/showthread.php?t=203827

My objective is to run an Excel macro from within PowerPoint. [All the macro does is change the row filtering for a data range in Excel, thus changing lines depicted on a chart].

So my PPT macro should (1) run the Excel macro which changes the chart, and then (2) update that chart in PPT which is linked to the Excel chart.

Here’s what I’ve tried:

Sub Test()
    Excel.Application.Run "'" & "C:\myPath\" & "PPT Macro Test.xlsm'!Steps"
    ActivePresentation.UpdateLinks
End Sub

It runs the “Steps” macro, updating the chart in Excel, but does not update the PPT chart.

So I adapted a technique from this post: How to update excel embedded charts in powerpoint? (hat tip brettdj).

Sub Test()
    Excel.Application.Run "'" & "C:\myPath\" & "PPT Macro Test.xlsm'!Steps"
    ChangeChartData
End Sub

Sub ChangeChartData()

    Dim pptChart As Chart
    Dim pptChartData As ChartData
    Dim pptWorkbook As Object
    Dim sld As Slide
    Dim shp As Shape

    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.HasChart Then
                Set pptChart = shp.Chart
                Set pptChartData = pptChart.ChartData
                pptChartData.Activate
                Set pptWorkbook = pptChartData.Workbook
                On Error Resume Next
                'update first link
                pptWorkbook.UpdateLink pptWorkbook.LinkSources(1)
                On Error GoTo 0
                pptWorkbook.Close True
            End If
        Next
    Next

    Set pptWorkbook = Nothing
    Set pptChartData = Nothing
    Set pptChart = Nothing

End Sub

Now it works as hoped, but it pauses while it opens, saves & closes the workbook. It’s a fairly large file, so this is an unacceptable delay during a presentation. Is there a way to run a macro in an Excel workbook which is already open “behind the scenes”, without reopening and closing it?

Thanks in advance.

I don't see anything in this code that opens an Excel file. Linked chart objects are notoriously difficult to work with, especially if you are trying to update data in real-time while PPT is in Presentation mode. Also, why are you looping over every chart in the PPT presentation? If there's only one chart/slide that needs updating, it is not optimal to loop over each slide, and each shape/chart... — David Zemens
@David Zemens (1) Yeah, I had to step thru to see what opens the workbook: "pptChartData.Activate". Surprised me, too. (2) I do not need to loop thru every chart (and I intended to modify that later), but I stuck with brettdj's approach initially to avoid changing multiple things at once. — Keifffer
I tested mine with a PPT command button and it worked as expected. if you'd like, I can put my sample files on Google Drive and share with you to show how they work. let me know. — David Zemens
@David Zemens Yes, please! I promise to share lessons learned with all others. — Keifffer
The Activate method of the ChartData will always open the file and bring it to foreground, this is some sort of "feature, not a bug" per Microsoft, or in any case it's absolutely designed and intended that way, documented as such. Poor design IMO! There is allegedly a way to circumvent this using a With block, but I don't think I've ever been able to get it to work as they suggest. — David Zemens

David Zemens David Zemens · Accepted Answer · 2017-04-24T17:43:18

In my brief testing, assuming the workbook is already open, then the data should update in real-time based on the Excel procedure. You should not need to call the ChangeChartData procedure from PowerPoint at all.

Sub Test()
    Excel.Application.Run "'" & "C:\myPath\" & "PPT Macro Test.xlsm'!Steps"
End Sub

This avoids the (presumably) resource-intensive task of the Save method against a very large Excel file, which when called from your PPT is being done against every chart, regardless of need, and which seems a very likely culprit for unnecessarily long runtime.

There may be some exceptions based on how the Test procedure is invoked from PowerPoint, and if you observe otherwise you should please add more detail (minimally: how the procedure is being run whilst the PPT is in Presentation Mode)

This answer is promising though, it has some apparent caveats (both files must be open, the Excel file should be the only Excel file open, etc.). I haven't tested other scenarios to see if it still works. It does appear to be working for me:

Set pres = Presentations("Chart.pptm") 'ActivePresentation, modify as needed.
' Make sure you reference correct shape name on the next line:
pres.Slides(1).Shapes("Chart1").LinkFormat.Update

In your implementation, perhaps:

For Each sld In ActivePresentation.Slides
    For Each shp In sld.Shapes
        If shp.HasChart Then
            Set pptChart = shp.Chart
            pptChart.LinkFormat.Update
        End If
    Next
Next

Regarding the Activate method of the ChartData object, MSDN Notes that:

You must call the Activate method before referencing this property; otherwise, an error occurs.

This is by design and "wont' be changed", but I've never spoke to anyone who understands why this is considered a good or desireable UI experience...

This self-answered question from a few years ago suggests you can avoid the Activate requirement, but I do not think this is accurate -- I can't replicate it and I can't find any other sources which indicate this can be done.

Run an Excel Macro to Update a Powerpoint Linked Chart (Workbook Already Open)

2 Answers