PPT链接的文件不可用

问题描述

　　用VBA更新过PPT图表里的数据：　　

Public Sub EqualRangeAsValuesToChart(ranFrom As Range, ranTo As Range)
    Dim ran As Range, offset_x&, offset_y&
    For Each ran In ranFrom.Cells
        offset_x = ran.Row - ranFrom.Cells(1, 1).Row
        offset_y = ran.Column - ranFrom.Cells(1, 1).Column
        ranTo.Offset(offset_x, offset_y).Value = ran.Value
    Next
End Sub

　　然后保存再打开后，就弹出如下消息：

分析原因

　　如果只更新里面的数据，没有定向，会导致图表数据源的链接不会自动更新，因此必须保证它的数据源链接没有中断！

解决办法

　　要先激活此图表，更新完数据后再关闭它！

 1 Public Sub EqualRangeAsValuesToChart(ranFrom As Range, shpChart As PowerPoint.Shape, ranChartTo As Range)
 2     Dim ran As Range, offset_x&, offset_y&
 3     shpChart.Chart.ChartData.Activate
 4     Set wks = shpChart.Chart.ChartData.Workbook.Worksheets(1)
 5     For Each ran In ranFrom.Cells
 6         offset_x = ran.Row - ranFrom.Cells(1, 1).Row
 7         offset_y = ran.Column - ranFrom.Cells(1, 1).Column
 8         wks.Range(ranChartTo.AddressLocal).Offset(offset_x, offset_y).Value = ran.Value
 9     Next
10     shpChart.Chart.ChartData.Workbook.Close
11 End Sub

更新代码