VB Powerpoint Automation - Charts Do Not Retain Data When Edited

[ApocY85]

With the function of code displayed below, I am editing powerpoint charts to update them with the most current data from an excel document. Everything works perfect except for when a person starts to edit a chart, all the data values in the chart worksheet appear to be restored to their previous values. Is there a solution to this problem? It doesn't make sense why Powerpoint would save both the old values and the new, and suddenly revert to the older ones for no reason or with no warning.


Private Sub CopyExportData(ByVal excelFileLoc As String, ByVal slideNo As Integer, ByVal xStart As Integer, ByVal yStart As Integer, ByVal xEnd As Integer, ByVal yEnd As Integer, ByVal transpose As Boolean)
Dim excWB As Excel.Workbook
Dim chartHolder As PowerPoint.Shape
Dim chart As Graph.chart
Dim x, y As Integer

Set excWB = excApp.Workbooks.Open(excelFileLoc)
Set chartHolder = oPPTPres.Slides(slideNo).Shapes(2)
Set chart = chartHolder.OLEFormat.Object

With chart.Application.DataSheet
.Rows.Clear
.Columns.Clear

For x = xStart To xEnd
For y = yStart To yEnd
If transpose Then
.Cells(y, x).Value = excWB.Worksheets(1).Cells(x + 1, y)
Else
.Cells(x, y).Value = excWB.Worksheets(1).Cells(x + 1, y)
End If
Next
Next
End With

excWB.Close

Set chart = Nothing
Set chartHolder = Nothing
Set excWB = Nothing

End Sub

If anyone could help, I'd greatly appreciate it!!