通过Excel VBA偶尔停止PowerPoint图表交互

我遇到ChartData属性的一些问题，我希望你们可以帮忙。 简短的版本是，当运行代码时，它会停止，通常在试图随机打开/关闭ChartData属性时。 然后我点击简历并继续运行。

较长的版本，我试图用多个工作簿中的大约30张幻灯片更新PowerPoint演示文稿，这些幻灯片可以在服务器上找到。 当我运行代码时，它通常在与图表背后的工作簿交互时停止。

我已经尝试了/没有引用，事先激活或选择ChartData父对象，但我仍然得到相同的错误：

方法工作簿对象chartdata失败

我是否错过了某些东西，或Excel没有正确地说出“PowerPoint”？

现在到代码：

Option Explicit

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public ppApp As PowerPoint.Application
Public ppPres As PowerPoint.Presentation
Public ppSlide As PowerPoint.Slide
Public ppShape As PowerPoint.Shape
Public ppChart As PowerPoint.Chart
Public ppChartData As PowerPoint.ChartData
Public ppSeries As PowerPoint.Series

Public Wb as Workbook

'...

Sub PPT_Slide2 ()

Dim sPathWb As String
Dim Wb_Chart As Workbook
Dim Ws_Chart As Worksheet

sPathWb = ThisWorkbook.Path & "[server folder partial address]" 

Application.DisplayAlerts = False
    Set Wb = Workbooks.Open(sPathWb & "file.xlsm")
Application.DisplayAlerts = True


For Each ppShape In ppSlide.Shapes
    'ppShape.Select
    If ppShape.Name = "Chart 1" Then

        Set ppChart = ppShape.Chart
        Set ppChartData = ppChart.ChartData
        ppShape.Chart.ChartData.Activate
        Set Wb_Chart = ppChartData.Workbook
        Set Ws_Chart = Wb_Chart.Worksheets(1)
        Ws_Chart.Activate

        Range(Range("A1"), Range("A1").SpecialCells(xlCellTypeLastCell)).ClearContents

        Windows(Wb.Name).Activate
        Wb.Sheets("Financial").Activate

        Application.CutCopyMode = False

        Set Rng = Range(Range("E2"), Range("E2").End(xlToRight))
        Rng.Copy

        ppApp.Activate
        Ws_Chart.Activate
        Range("B1").PasteSpecial xlPasteValues

        Windows(Wb.Name).Activate

        Cells(Rows.Count, "A").End(xlUp).End(xlUp).Activate

        Set Rng = Range(ActiveCell, ActiveCell.Offset(14, 0))
        Rng.Copy

        ppApp.Activate
        Ws_Chart.Activate
        Range("A2").PasteSpecial xlPasteValues
        Application.CutCopyMode = False

        Windows(Wb.Name).Activate

        Cells(Rows.Count, "E").End(xlUp).End(xlUp).Activate
        Set Rng = Range(ActiveCell, ActiveCell.Offset(14, 0).End(xlToRight))

        Rng.Copy

        ppApp.Activate
        Ws_Chart.Activate
        Ws_Chart.Range("B2").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        ppShape.Chart.ChartData.Workbook.Close True

        '--> refreshing the chart here; 
        'for whatever reason the PowerPoint Chart does not show the newly added values 
        'unless in debug mode or if I set a wait time

        With ppChart
            .ChartData.Activate
            .Refresh
            Sleep 100
            .ChartData.Workbook.Close
        End With

    End If
Next

Wb.Close False

Set Wb = Nothing
Set Ws_Chart = Nothing
Set Wb_Chart = Nothing
Set ppChartData = Nothing
Set ppChart = Nothing

End Sub

链接地址: http://www.djcxy.com/p/45689.html

上一篇: Occasional stop on PowerPoint Chart interactions through Excel VBA

下一篇: Gray Borders around Excel Chart in PowerPoint (2010)