Occasional stop on PowerPoint Chart interactions through Excel VBA
I am having a few issues with the ChartData property and I am hoping you guys can help. The short version is that when running the code it just stops, usually when trying to open/close the ChartData property, at random. Then I click resume and it continues to run.
The longer version, I am trying to update PowerPoint presentation with about 30 slides from several workbooks that can be found on a server. When I run the code, it usually stops when interacting with the workbook behind the Chart.
I've tried with/without references, activating or selecting the ChartData parent objects beforehand, but I still get the same error:
Method workbook object chartdata failed
Am I missing something or does Excel not speak "correctly" PowerPoint ?
Now to the code:
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