MS powerpoint graphs linked to Excel...code inside. 1

[rajeev00]

I am using these two macros to update and format graphs in powerpoint that are linked to excel.

The problem with updateallgraphs is that ograph.application.update works only with when links are set to automatic update. what i want is either for this code to work with links update set to manual or find a piece of code that can break the links after they have been updated.

The problem with formatallgraphs is a very curious one. The code seems to work just fine when i step throught it (F8) but does not work when i run it (F5)


Also is there a way to find out all the methods associated with ograph.application.


Thanks for you help.

Rajeev

**************************************8
Sub UpdateAllGraphs()

Dim oShape As Shape
Dim oSlide As Slide
Dim oGraph As Object
For Each oSlide In ActivePresentation.Slides
' Loop through all the shapes on the current slide.
For Each oShape In oSlide.Shapes
' Check whether the shape is an OLE object.
If oShape.Type = msoEmbeddedOLEObject Then
' Check whether the OLE object is a Graph 8 object.
'
If oShape.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'
' Found a graph; obtain object reference, and
' then update.
'
Set oGraph = oShape.OLEFormat.Object
oGraph.Application.Update

' Now, quit out of the MSGraph program. This frees
' memory, and prevents any problems. Also, set oGraph equal
' to Nothing to release the object.
'
oGraph.Application.Quit
Set oGraph = Nothing
End If
End If
Next oShape
Next oSlide
End Sub


*****************************************


Sub FormatAllGraphs()

Dim oShape As Shape
Dim oSlide As Slide
Dim oGraph As Object
'
' Loop through all the shapes on the current slide.
'
For Each oShape In oSlide.Shapes


'
' Check whether the shape is an OLE object.
'
If oShape.Type = msoEmbeddedOLEObject Then


'
' Check whether the OLE object is a Graph 8 object.
'
If oShape.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'
' Found a graph; obtain object reference, and
' then update.
'
Set oGraph = oShape.OLEFormat.Object

' find the row number that has the first 0 value

For j = 1 To 10
If oGraph.Application.datasheet.Cells(j, 1).Value = 0 Then
beg_delindex = j
Exit For
End If
Next j

'MsgBox i & beg_delindex

' find the row number that has the first non 0 value after the 0 values ( the assumption(s))

For k = beg_delindex To 15

If oGraph.Application.datasheet.Cells(k, 1).Value <> 0 And oGraph.Application.datasheet.Cells(k, 1).Value <> "" Then
end_delindex = k - 1
Exit For
End If

Next k

' delete all the rows between the two extremes

For l = beg_delindex To end_delindex
oGraph.Application.datasheet.Rows(beg_delindex).Delete
' MsgBox l & "beg_delindex" & beg_delindex
Next l
' End With

'
' Now, quit out of the MSGraph program. This frees
' memory, and prevents any problems. Also, set oGraph equal
' to Nothing to release the object.
'
oGraph.Application.Quit
Set oGraph = Nothing
End If
End If


Next oShape
Next oSlide

End Sub

 

[PHV]

Also is there a way to find out all the methods associated with ograph.application.
Reference the Microsoft Graph x.0 Object Library in your VBA project and play with the Object Browser pane.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RodP]

Hi Everyone,

I've found this post about changing the formatting of a chart/datasheet of an MS graph object from within Powerpoint.

I'm currently using something similar to this code to cycle through all graph objects on the current slide / whole document to update them.

However, I now need to go into each chart now (A total of about 200!) and change the link to a different filename & path (the sheet name does change slightly too but the cell references remain the same). Please could someone help me by advising what code I need to use? I've looked through the MS Graph 9.0 VBA library but am still not sure how to go about it (plus you can't record a macro from within MS Graph to give you any clues).

Thanks in advance for the help.

RodP