Embedded Excel Chart Data Sheet not getting closed

[Suresh1980]

Hi All,

I have written code to update the total value of the chart to be update as a text box.

Everything works fine, the only challenge is all the Data sheet of the PowerPoint chart is not getting closed.

Not sure where is the bug, can someone please help me out.

Please find the attached code.

Thanks a ton in advance.

Suresh


Option Explicit

Sub ChangeChartData()

Dim pptChart As Chart
Dim pptChartData As ChartData
Dim pptWorkbook As Object
Dim Sld As Slide
Dim shp As Shape
Dim shp2 As Shape
Dim Cnt As Integer
Dim i As Integer

Cnt = 0
For Each Sld In ActivePresentation.Slides
i = 0
Cnt = Cnt + 1
'Sld.Select
For Each shp In Sld.Shapes


If shp.HasChart Then


Set pptChart = shp.Chart
Set pptChartData = pptChart.ChartData
pptChartData.Activate
Set pptWorkbook = pptChartData.Workbook
On Error Resume Next


i = i + 1
ActivePresentation.Slides(Cnt).Shapes("TotalBox" & i).TextFrame.TextRange.Text = "Total: " & pptWorkbook.worksheets(1).Range("g2")
ActivePresentation.Slides(Cnt).Shapes("Chart" & i).Select
' pptChart.SetElement (msoElementDataLabelNone)
' pptChart.SetElement (msoElementDataLabelShow)
' Application.DisplayAlerts = ppAlertsNone
' pptWorkbook.Close
' Application.DisplayAlerts = ppAlertsAll
'On Error GoTo 0


End If

' If Left(shp.Name, 8) = "TotalBox" Then
' i = i + 1
' ActivePresentation.Slides(Cnt).Shapes("TotalBox" & i).TextFrame.TextRange.Text = pptWorkbook.worksheets(1).Range("g2")
' End If

Next
Set pptWorkbook = Nothing
Set pptChartData = Nothing
Set pptChart = Nothing
ActivePresentation.Slides(1).Select
Next



End Sub

[mooseman]

Do you need to activate the datasheet?


pptChartData.Activate

This is the part that opens an instance of Excel to show the datasheet.
Datasheets should be able to be referenced without activating them.
Not sure if this will work, you may have to go to the sheet level .sheets(1)


Set pptWorkbook = shp.Chart.ChartData .Workbook