Run an Excel Macro to Update a Powerpoint Linked C

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.

Answer 1

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.

Answer 2

@David, thanks for the help. This (mostly) works:

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

Mostly. Your comments "it was working, then it wasn't, now it is" forced me into some troubleshooting. Here's the workaround:

• Open the PPT file, click update links
• Immediately, right click on the embedded/linked chart, select "Edit Data"
• This opens the Excel file (NOT read-only)
• Close Excel, without saving the file

Amazingly, it then runs by clicking the button in slideshow view, or stepping thru in the VB Explorer. Even more amazing, when it runs it doesn't open Excel--it just works in the background.

If I do NOT right click >> "Edit Data" first, it will ALWAYS open Excel & prompt for Read-Only/Notify/Cancel. Then I can't run the macro from PPT, and running it within Excel updates the chart only in Excel, not in PPT as well.

Alternately I tried "Slide1.Shapes(1).LinkFormat.AutoUpdate = ppUpdateOptionAutomatic" to see if that would set updating to automatic...it didn't.

If anyone can chime in with a fix to the workaround, I'd appreciate it. In the meantime, thanks David for your selfless perseverance, and I'll try to figure out how to give you credit for the answer.