Problems setting data into charts with VBA

[Cosmo]

I found that when setting data into different charts, the values don't retain their changes when the PPT file is closed. (the charts look like they have the new data, but upon opening them, they revert to their state before the changes were made) A snippet of the code I'm using to set the data:

With oPPTShape
If .Type = 7 Then ' Chart
If TypeName(.OLEFormat.Object) = "Chart" Then
.OLEFormat.Object.Application.datasheet.Range(mColumn & mRow).Value = theValue
ElseIf TypeName(.OLEFormat.Object) = "Workbook" Then
.OLEFormat.Object.Sheets("Sheet1").Range(mColumn & mRow).Value = theValue
End If
End If
End With
I found that opening and closing the charts will make the changes 'stick', so I run this code on each of the charts once the program is finished:
With oPPTShape
If .Type = 7 Then ' Chart
.OLEFormat.DoVerb Index:=1
End If
End With


Is there a better way of doing this? Is there something that can be done when the data is set into the chart to make it retain it's value automatically?

My main reason for asking this, is that I have recently been getting a dialog when setting data into Excel charts in PowerPoint that says "The object is not responding because the source application may be busy". The dialog is from Excel. I have stored references to the objects that I am setting data into in the presentation (a combination of tables, charts, and text fields), but I don't see what is causing this dialog, because I am not setting any references to Excel specifically, and I am not sure what would be keeping Excel busy after running the code which sets the data.

I tried the following:

With oPPTShape
If .Type = 7 Then ' Chart
.OLEFormat.Object.Application.DisplayAlerts = False
End If
End With

hoping that it would suppress this dialog, but it didn't work.

[John Wilson]

Maybe try the Update method?


.OLEFormat.Object.Application.Update
.OLEFormat.Object.Application.Quit

[Cosmo]

Thanks for the response. Yes, I had tried to update (I had those lines commented out in my original code, but I don't recall if it was because they didn't suppress the dialog or if it didn't update the charts properly)

Surprisingly, when I tested it just now with the update code included, it actually showed the Excel dialog even sooner than before (there are 6 different tabs setting data into about 8 different charts, and the dialog showed up before I even got to the last tab). Just now, after disabling that code again, it took several minutes to show the dialog (It actually started coming up while I was typing this, with the application in the background)

I am stumped. I will look through my code again, but to my knowlege, those lines in the class which sets the data into the chart are the only area where I am accessing the Excel object. If anyone has any other suggestions, I'd be very appreciative to hear.