I wrote a macro that updates the data of a powerpoint graph. It basically selects a range in an excel table, copies it, and then goes to the powerpoint datasheet of the graph and then pastes it (see attached code). However, the bars of the graph disappear after the auto-paste by macro. I believe it is a formatting issue because when I do the same process manually (manual copy/paste) it works fine. Or after the macro-paste if I edit the cell in datasheet and simply press ENTER, it corrects the data and bar shows up. But ofcourse it defeats the purpose of automating the whole process.. What can I do to avoid this and make the auto-paste work correctly?.. Thanks in advance.
Please see attached illustration.gif. The two columns were pasted by macro, hence no bars showed up.. Then I manually edited the first column (just double clicked on it and then hit ENTER) and the bar showed.
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim rngNewRange As Excel.Range
Dim oGraph As Object
'get the open ppt file
Set oPPTApp = GetObject(, "Powerpoint.Application")
'Set PowerPoint to be Visible.
oPPTApp.Visible = msoTrue
'select the sheet
'Set range, select and copy
Set rngNewRange = ActiveSheet.Range("A1:Z99")
'work on the slide 12
For Each oPPTShape In .Shapes
'Check to see whether shape is an OLE object.
If oPPTShape.Type = msoEmbeddedOLEObject Then
'Check to see whether OLE object is a Graph 2000 object.
If oPPTShape.OLEFormat.progID = "MSGraph.Chart.8" Then
'Set oGraph to the Graph object on the slide.
Set oGraph = oPPTShape.OLEFormat.Object
'Paste the cell range into the upper leftmost cell of the graph