0

I am trying to edit the source data to a pre formatted waterfall chart in PowerPoint. However, the code crashes when trying to access the .ChartData.Workbook property.

Code crashes at first line:

Set wbChart = cfSlide.Shapes("CFChart").chart.ChartData.Workbook Set wsChart = wbChart.Worksheets(1)

The code works perfectly on old charts.

Thanks in advance.

L. Kleiven
  • 23
  • 5
  • 1
    I don't envy you for all the troubles the waterfall chart has given you. You should try to get a free year of office from Microsoft for all your troubles :P – RGA Jul 18 '16 at 15:04

2 Answers2

0

From 2007 onwards, I believe it is necessary to Activate the workbook. This is a really terrible design/implementation, but in my experience it is necessary.

cfSlide.Shapes("CFChart").chart.ChartData.Workbook.Activate
Set wbChart = cfSlide.Shapes("CFChart").chart.ChartData.Workbook
wbChart.Application.WindowState = -4140    '## Minimizes Excel
Set wsChart = wbChart.Worksheets(1)

This gets nasty because then you have to minimize it and/or .Close it for minimal UI interruption. I like to minimize it immediately, and close it when finished, which you could do wbChart.Close

I think I have read elsewhere that you can avoid this by using a With block, but I've not been able to successfully implement that, e.g.:

With cfSlide.Shapes("CFChart").chart.ChartData.Workbook
    Set wsChart = .Worksheets(1)
    ' etc...

End With
David Zemens
  • 53,033
  • 11
  • 81
  • 130
0

In 2016 Powerpoint has a way to access the data behind a chart (not an Excel embedded object) XLWorkbook being the Excel file that holds the new data

For Each oSH In ActivePresentation.Slides(2).Shapes

                    With oSH.Chart.ChartData

                            'this updates the values in the datasheet
                            .Workbook.sheets(1).Range("A1:B" & lastrow ).Value = xlWorkBook.sheets(2).Range("A3:B" & lastrow).Value

                    End With
mooseman
  • 1,997
  • 2
  • 17
  • 29