如何在PowerPoint中更新Excel嵌入式图表？ [英] How to update excel embedded charts in powerpoint?

问题描述

我有30张由excel创建的图表，并粘贴到了PowerPoint幻灯片上。每个月，我必须通过手动单击图表并进行编辑来更新这30个嵌入式图表。

I have 30 charts that were created from excel and were pasted onto powerpoint slides. Every month, I have to update these 30 embedded charts by manually clicking on the charts and edit.

我知道有一个使用选择性粘贴的选项，因此可以通过单击更新链接来自动更新图表中的数据。但是，某些用户需要编辑我的图表。粘贴特殊选项不允许用户编辑图表。因此，我无法使用此粘贴特殊选项。

I am aware there is an option to use paste special, so that the data in the charts can be updated automatically by clicking the update links. However, my charts needs to be edited by some users. Paste special option does not allow users to edit the charts. Hence, I am unable to use this paste special option.

我认为解决方案在于在Powerpoint中编写vba。这里有任何专家可以提供编写此VBA代码的功能，以允许在Powerpoint中更新所有图表吗？我当前使用的是Powerpoint2007。非常感谢您的帮助。

I think the solution lies in writing a vba in powerpoint. Can any expert here offer to write this vba code to allow all the charts to be updated in powerpoint? I am currently using powerpoint 2007. Your assistance is greatly appreciated.

推荐答案

如果您需要编辑图表，那么您显然可以需要编辑基础的Excel文件，或者能够在PowerPoint中编辑

If you need to edit the charts then clearly you will either need to edit the underlying Excel files, or be able to edit in PowerPoint

当您使用提供完全Excel支持的PowerPoint2007（与具有数据表的PowerPoint 2003不同）时，会

As you are using PowerPoint2007 which provides full Excel support (unlike PowerPoint 2003 which has a datasheet) I would

第1部分

1. 链接您的Excel文件数据到每个图表下方的Excel数据


2. 提供直接使用该数据或将其替换为用户数据的功能

这为您提供了一个灵活的解决方案，只是不能通过PowerPoint菜单的更新链接命令自动更新每个图表的Excel。

This gives you a flexible solution, except that Excel underlying each chart cannot be updated automatically via a PowerPoint menu Update Links command.

第2部分

您可以使用下面的代码测试每一个r每张幻灯片上的每个形状都有一个图表。如果是这样，此代码将更新图表下方Excel文件中的第一个Excel链接（可以调整此部分以处理多个链接）

You can use the code below to test each whether each shape on each slide has a chart. If so this code will update the first Excel link in the Excel file underneath the chart (this part can be tweaked to handle multiple links)

    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

这篇关于如何在PowerPoint中更新Excel嵌入式图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！