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

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

　　

 

分析原因

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

解决办法

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

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