auto updating excel links in XP

[Guest]

In PP 97 I used the UpdateAllGraphs VB routine to automatically update the
(set to manual) paste links and the embedded (graph 8) links in about 50
PPT's each month.

I just upgraded to XP, redesigned the PP's and now, only the paste links
update. Here is the code I am using. Can anyone tell me why it no longer
works? I tried the mvps add-in and it changed all my charts!

Thanks, Mike

Sub UpdateAllGraphs()
Dim shapeObject As Shape
Dim lSlideCount As Long
Dim lCount As Long
Dim oGraph As Object
Dim bLaunch As Boolean

'Update the Excel Objects first
For Each sld In ActivePresentation.Slides
For Each sh In sld.Shapes
If sh.Type = msoLinkedOLEObject Then
sh.LinkFormat.Update
End If
Next
Next

'initialize counter
lCount = 0

'Get the number of slides in the presentation
lSlideCount = ActivePresentation.Slides.Count

'Loop through each slide
For lCount = 1 To lSlideCount

'look at all the shapes on the current slide
For Each shapeObject In ActivePresentation.Slides(lCount).Shapes

'Is this a OLE object
If shapeObject.Type = msoEmbeddedOLEObject Then

'if it is an OLE object, then is it a Graph 8 (I changed
this to 10) object
If shapeObject.OLEFormat.progID = "MSGraph.Chart.10" Then

'found a graph...update it
Set oGraph = shapeObject.OLEFormat.Object
oGraph.Application.Update

bLaunch = True
End If
End If


Next shapeObject
Next lCount

If bLaunch Then
oGraph.Application.Quit
Set oGraph = Nothing
End If
End Sub

 

[Steve Rindsberg]

In PP 97 I used the UpdateAllGraphs VB routine to automatically update the
(set to manual) paste links and the embedded (graph 8) links in about 50
PPT's each month.

I just upgraded to XP, redesigned the PP's and now, only the paste links
update. Here is the code I am using. Can anyone tell me why it no longer
works? I tried the mvps add-in and it changed all my charts!

If you're running this on PPTs with embedded graphs from PPT8, I'd expect it
NOT to work because of this:

'if it is an OLE object, then is it a Graph 8 (I changed
this to 10) object
If shapeObject.OLEFormat.progID = "MSGraph.Chart.10" Then

Your code is looking for MSGraph.Chart.10 objects and you've still got version
8 objects in your presentation. ISTR that they get updated once you activate
them, but not before.

Try:

If Instr(shapeObject.OLEFormat.progID, "MSGraph.Chart") > 0 Then

That'll nail 'em no matter which version of Chart made 'em.

 

[Guest]

Steve,

It updated about 75% of the charts, but changed most of them, ie., striped
out data tables and resized charts. Why is PPT doing this? Am I doing
something wrong?

 

[Steve Rindsberg]

Steve,

It updated about 75% of the charts, but changed most of them, ie., striped
out data tables and resized charts. Why is PPT doing this? Am I doing
something wrong?

I don't know, really. One other thing I'd try is not trying to keep MSGraph open
for the whole run. For each graph, update it, quit graph then move along.