Editing datasheets for MSO Graphs: must PowerPoint be visible?

[Dr.K]

I have a VBA app in Excel that opens a PowerPoint Application with VISIBILITY = FALSE, opens a template, pastes in some Excel objects as metafile pics, saves it and closes it.

Now I've been tasked with updating a MSO Graphs chart that already exists in the PowerPoint Template. I used various code snippets, including this one:
http://www.mahipalreddy.com/vba/codes62684.htm#vba2

This gave me code like this:

'get a handle on the graph object
For Each ppShape In ActSlide.Shapes
'Check to see whether shape is an OLE object.
If ppShape.Type = msoEmbeddedOLEObject Then
'Check to see whether OLE object is a Graph 2000 object.
'( ProgID is case sensitive)
If ppShape.OLEFormat.progID = "MSGraph.Chart.8" Then
'Set oGraph to the Graph object on the slide.
Set ppOLE = ppShape.OLEFormat.Object
End If

End If

Next ppShape
'update the graph data
ppOLE.Application.DataSheet.Range("D1").Value = arrIndicators(0)
ppOLE.Application.DataSheet.Range("D2").Value = 1 - arrIndicators(0)
ppOLE.Application.DataSheet.Range("C1").Value = arrIndicators(1)
ppOLE.Application.DataSheet.Range("C2").Value = 1 - arrIndicators(1)
ppOLE.Application.DataSheet.Range("B1").Value = arrIndicators(2)
ppOLE.Application.DataSheet.Range("B2").Value = 1 - arrIndicators(2)
ppOLE.Application.DataSheet.Range("A1").Value = arrIndicators(3)
ppOLE.Application.DataSheet.Range("A2").Value = 1 - arrIndicators(3)

Set ppOLE = Nothing

I originall had a WITH statement for that last block, but I've been trying all kinds of crazy things with code to get it to work. Basically, the numbers don't update.

My current theory is that you can only edit MSO Graphs in PowerPoint WHEN PowerPoint is visible... This is becuase every code snippet I've seen has this line in it:

ppApp.Visible = msoTrue


However, making PowerPoint visible at this point screws up everything, becuase i don't even have a window for the presentation.

My current work around is to use an Excel Chart as a template, paste the new data in, copy the Chart, and then paste it into PowerPoint as a Metafile pic. However, it would be easier to just edit the MSO Graph Object...

Anyone run into problems with this before?

[Cosmo]

I don't know if it's the same problem you are having, but I found long ago that if I set data into a chart, it wasn't retained in the chart when the chart was edited, unless the chart was 'opened' in the code after setting the data. Running this seemed to fix the problem for me:

ppShape.OLEFormat.DoVerb Index:=1

I also believe the slide had to be the active slide for this to work.

Hope this helps.

[Dr.K]

Yeah, I was thinking that using verbs to open and close the Datasheet might fix it...


But if it has to be the active Slide, then doesn't that mean that it has to have a Window, and therefore PowerPoint has to be Visible?

When the ppApp is not visible, I don't think there is active anything...

[Cosmo]

Yeah, I was thinking that using verbs to open and close the Datasheet might fix it...


But if it has to be the active Slide, then doesn't that mean that it has to have a Window, and therefore PowerPoint has to be Visible?

When the ppApp is not visible, I don't think there is active anything...
I'm not sure, all of my charts are edited from a VBA in PowerPoint, so it's already running in my case (although, I am working with a hidden window so it doesn't actually show the charts opening/closing in my program). You might not need to have PPT active for it to work, but I can't say for sure.

How are you referencing PPT/Presentation? What sets the value of your variable 'ActSlide'?