如何更新图表?

本文介绍了如何更新图表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带有约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