Why doesn't changing the data point value(s) updates the value in backend excel of a ppt chart?

Question

I stumbled onto something very interesting. So I have two charts. They have the same series names but different data. Using the below code, I replaced all the values of the data points of first chart with the values in second chart. On the front end the code seemed to have worked perfectly. All the data points in first chart now reflects the change. But interestingly enough, if I open the backend excel of first chart, it still has the old values. The change was reflected only in the front end, not in backend.

Can anybody explain to me why is this so and is there any method by which I can force the chart's embedded excel to take on values that it's data points shows.

   For trial_a = 1 To thischart.FullSeriesCollection.count
          For trial_b = 1 To thatchart.FullSeriesCollection.count
              If thischart.FullSeriesCollection(trial_a).Name = thatchart.FullSeriesCollection(trial_b).Name Then
              
                       thischart.FullSeriesCollection(trial_a).Values = thatchart.FullSeriesCollection(trial_b).Values
                       thischart.FullSeriesCollection(trial_a).XValues = thatchart.FullSeriesCollection(trial_b).XValues
               
              End If
           Next trial_b
   Next trial_a

Answer 1

I've been playing around with this since Jon Peltier pointed out that there is a way to get at the Workbook object. Check this out:

Sub CheckSelectedChart()

Dim oShp As Shape
Dim oChart As Chart
Dim oWorkbook As Workbook
Dim oActiveSheet As Worksheet

Set oShp = ActiveWindow.Selection.ShapeRange(1)
Set oChart = oShp.Chart
Set oWorkbook = oChart.ChartData.Workbook

' This is the step I was always missing!
oChart.ChartData.ActivateChartDataWindow

Set oActiveSheet = oWorkbook.ActiveSheet

Debug.Print "Break!"

End Sub

When I had explored this in the past, I got as far as inspecting the ChartData object that's part of the chart -- however, if you're just working with a normal chart selection, the Workbook object always looked empty (no Worksheets, etc.)

It turns out that you have to run the ActivateChartDataWindow method, which pops up the datasheet.

Once you do that, the Workbook object is populated, and you're able to assign the chart's data sheet to a Worksheet object and treat it like an Excel Worksheet, as far as I can see.

/edit Here is some more code to help store the source worksheet data and then input it into the destination worksheet, which more clearly gets at the problem you were trying to solve.

While I'm seeing that in your initial example you have some conditionals in there, where the data is only replaced if the series name matches, it should be pretty easy for you to adapt this example, where we copy the entire data table from one embedded chart to another:

' assign oActiveSheet to the underlying worksheet of the source chart.

With oActiveSheet.UsedRange
    rowCount = .Rows.Count
    columnCount = .Columns.Count
    
    ReDim dataArray(1 To rowCount, 1 To columnCount)

    For i = 1 To rowCount
        For j = 1 To columnCount
            dataArray(i, j) = oActiveSheet.Cells(i, j).Value 
        Next
    Next
End With

Then assign oActiveSheet to the destination chart's worksheet (as seen at the top):

With oActiveSheet
   ' clear original data 
   .Cells.Clear
    For i = 1 To rowCount
        For j = 1 To columnCount
            .Cells(i, j).Value = dataArray(i,j)
        Next
    Next
End With

One final note, since the data ranges may not exactly match in size, you'll need to run something like this to reset the data range after copying all of the data over:

oChart.SetSourceData "='Sheet1'!$1$1:$" & ColLetter(columnCount) & "$" & rowCount

Where the following function converts your max column from an integer to Excel alphanumeric columns:

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Again, this is a little convoluted, but the data labels are basically just text unconnected to the underlying data. So to affect the actual data, leave the data labels alone and get at the underlying worksheet.

Answer 2

To my knowledge, that backend Excel file driving an embedded PPT chart is not accessible using the VBA object model. There are extensive options for using VBA to affect the look and feel of the chart itself, but I’ve never found a way to assign the underlying Excel to a Worksheet object (or similar) and directly manipulate the values in the data sheet. Everything applied to the chart is superficial and “disconnected” from the sheet.

If the PPT chart is linked to data stored in an external Excel file, it’s actually quite easy to get at that Excel file and manipulate the source data (I’ve written an addin that works this way to get around the problem). But for embedded charts, the data sheet is functionally invisible to VBA, as far as I can tell.