Update PowerPoint chart by VBA

D

ddwmoq

I have several charts in a PowerPoint file linked with a hugh Excel
spreadsheet. I have written a VBA program to update the charts but it
does always not work. Sometimes I have to run it repeatedly to update
all the charts. Can anyone help?

Also, is there anyway to retrieve the source file name
programmatically?

Thanks.

------------------------------------------------------------------------------------------------------------------------------------
Sub Update_chart()
Dim sld As Slide, sh As Shape, oChart As Object, x As String

For Each sld In ActivePresentation.Slides
For Each sh In sld.Shapes
If sh.Type = msoEmbeddedOLEObject Then
If sh.OLEFormat.ProgID = "MSGraph.Chart.8" Then
Set oChart = sh.OLEFormat.Object
With oChart
x = x + "Slide " + Str(sld.SlideNumber) + ": "
If .HasTitle Then
x = x + vbTab + .ChartTitle.Text
Else
x = x + vbTab + "[no title]"
End If
DoEvents
.Application.Update
DoEvents
.Application.Quit
End With
Set oChart = Nothing
End If
x = x + vbCr
End If
Next
Next

DoEvents
MsgBox "The following charts were updated: " + vbCr + x
End Sub
 
D

David M. Marcovitz

I don't know about updating charts specifically, but when you update a
lot of things in PowerPoint, the For Each loop isn't necesarily a good
idea. My guess is that as you run through the shapes, the shape order is
getting changed, so you mean to update shapes A, B, and C, but after
updating shape A, it gets shifted in the order to the end, so your new
order is shapes B, C, and A. Now your loop has finished with the first
shape and moves on to the second shape. You want it to go from A to B,
but the order is changed. The new first shape is B. It thinks it is done
with the first shape and goes onto C (skipping B).

The trick is to write your own loop to loop backward through the shapes,
so when something gets shifted to the end, it will be in a slot you have
already passed, but it will be a shape you have already passed. Something
like:

For i = oSld.Shapes.Count to 1 Step -1
oSld.Shapes(i)....
Next i

--David

--
David M. Marcovitz
Microsoft PowerPoint MVP
Director of Graduate Programs in Educational Technology
Loyola College in Maryland
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/
 
D

ddwmoq

Thank you guys.

For some unknown reasons, my original program works when I changed the
code "oChart.Application.Update" to "oChart.Refresh".

I still can't figure out how to retrieve (and change) the source file
name from MS Graph. It will be extremely useful when I am required do
office work at home.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top