如何更新图表? [英] How to update a chart?

问题描述

我有一个带有约200张幻灯片的PowerPoint演示文稿.每张幻灯片都有一张图表，并通过指向主xlsx文件的链接每月更新一次数据.

I have a PowerPoint presentation with ~200 slides. Each slide has one chart, with data that is updated monthly by a link to a master xlsx file.

要不在图表中显示空值(未来月份)，请打开每个图表的数据编辑器(在图表上单击鼠标右键>编辑数据...")，然后选择直到当前月份的范围.

To not show empty values (future months) in the charts, I open the data editor (chart right click > Edit data...) of every chart and select the range until the current month.

我在PowerPoint中为此编写了一个宏:

I wrote a macro for it in PowerPoint:

Sub Refresh_slides()

    For i = 1 To ActivePresentation.Slides.Count

        Set ObjSlide = ActivePresentation.Slides(i)

        On Error Resume Next

        Set mychart = ObjSlide.Shapes("Chart 3").Chart
        mychart.Select
        mychart.ChartData.Activate
        Set wb = mychart.ChartData.Workbook
        Set ws = wb.Worksheets(1)

        Application.Run "Refresh_slides_AUX.xlsm!atual_slide"

        wb.Close True

    Next

End Sub

Refresh_slides_AUX.xlsm 是一个辅助宏工作表，用于选择每个图表的正确范围(据我所知，因为PowerPoint VBA没有选择这样做):

Refresh_slides_AUX.xlsm is an auxiliary macro worksheet to select the correct range of each chart (because PowerPoint VBA, as far as I know, don't have an option to do it):

Sub atual_slide()

    Windows("Gráfico no Microsoft PowerPoint").Activate
    ActiveSheet.ListObjects("Table1").Resize Range("$A$1:$I$23")
    ActiveWindow.Close SaveChanges:=True


End Sub

结果是:

之前:后:

它可以工作，但是调整范围的大小后，图表不变，它们看起来仍然像之前的图片.

It works, but after the range is resized the charts don’t change, they still looking like the BEFORE picture.

我知道这个过程是正确的，因为如果我手动进行大小调整(虚线的右角)，则图表将正确更新.

I know the process is right because if I do the resizing manually (right corner of the dotted line) the chart is updated correctly.

推荐答案

这是一个古老的问题，但是我找不到答案，因此这个问题成为我搜索的第一热门.

This is an old question but I had trouble finding an answer and this question came up as the first hit for my search.

我想发布一个Powerpoint vba例程，该例程具有一个ActivateChartDataWindow方法，我发现在更新数据后刷新图表是必需的.

I'd like to post a powerpoint vba routine that has an ActivateChartDataWindow method that I found was necessary to refresh the chart after I had updated the data.

Private Sub RefreshChart(slideName As String, shapeName As String, spName As String, dataRange As String)
    'Add reference to Microsoft ActiveX Data Object 6.x Library
    'Read data point info from SQL
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connStr As String
    Dim query As String
    Dim sld As Slide
    Dim shp As Shape
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet

    Set sld = ActivePresentation.Slides(slideName)
    Set shp = sld.Shapes(shapeName)
    Set xlWB = shp.Chart.ChartData.Workbook
    Set xlWS = xlWB.Sheets(1)
    xlWS.Range(dataRange).Clear
    Set conn = New ADODB.Connection 'CreateObject("adodb.connection")
    connStr = "Provider=SQLOLEDB;Data Source='" & SQLServer & "';Initial Catalog=WVCore;Integrated Security=SSPI;"
    conn.Open connStr
    Set rs = New ADODB.Recordset 'CreateObject("adodb.recordset")
    query = "exec " & spName 'usp_WVCRevenue
    With rs
        .ActiveConnection = conn
        .Open query
        xlWS.Range("A2").CopyFromRecordset rs 'Leave headings in tact
        .Close
    End With
    shp.Chart.ChartData.ActivateChartDataWindow
    xlWB.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

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