运行 Excel 宏以更新 Powerpoint 链接图表(工作簿已打开) [英] Run an Excel Macro to Update a Powerpoint Linked Chart (Workbook Already Open)

问题描述

更新&交叉发布自:http://www.ozgrid.com/forum/showthread.php?t=203827

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

我的目标是在 PowerPoint 中运行 Excel 宏.[宏所做的就是更改 Excel 中数据范围的行过滤，从而更改图表上描绘的线条].

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].

所以我的 PPT 宏应该 (1) 运行改变图表的 Excel 宏，然后 (2) 在链接到 Excel 图表的 PPT 中更新该图表.

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.

这是我尝试过的:

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

它运行Steps"宏，更新Excel中的图表，但不更新PPT图表.

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

所以我从这篇文章中采用了一种技术:如何更新 excel在 powerpoint 中嵌入图表?(帽子提示 brettdj).

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

现在它按预期工作，但在打开、保存和保存时会暂停关闭工作簿.这是一个相当大的文件，因此在演示过程中这是不可接受的延迟.有没有办法在幕后"已经打开的 Excel 工作簿中运行宏，而无需重新打开和关闭它?

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?

提前致谢.

推荐答案

在我的简短测试中，假设工作簿已经打开，那么数据应该基于 Excel 程序实时更新.您根本不需要从 PowerPoint 调用 ChangeChartData 过程.

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

这避免了 Save 方法对一个非常大的 Excel 文件的(大概)资源密集型任务，当从你的 PPT 调用时，它正在针对每个图表完成，不管需要，这似乎是不必要的长时间运行的罪魁祸首.

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.

根据 Test 过程是如何从 PowerPoint 中调用的，可能会有一些例外，如果您观察到其他情况，您应该添加更多细节(至少:在 PPT 运行时该过程是如何运行的在演示模式)

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)

这个答案很有希望，但它有一些明显的警告(两个文件都必须是打开，Excel 文件应该是唯一打开的 Excel 文件，等等).我还没有测试其他场景，看看它是否仍然有效.它似乎对我有用:

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

在您的实施中，也许:

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

<小时>

关于 ChartData 对象的 Activate 方法，MSDN 注释:

在引用该属性之前必须调用Activate方法；否则会出错.

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...

这个 几年前的自我回答问题表明您可以避免Activate 要求，但我认为这不准确——我无法复制它，也找不到任何其他来源表明可以做到这一点.

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.

这篇关于运行 Excel 宏以更新 Powerpoint 链接图表(工作簿已打开)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！