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/
 
S

Steve Rindsberg

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?

With charts and/or data linked to Excel, yes. But you're using MSGraph charts, or so
it seems from your code sample (and thanks for posting that, by the way). You can't
get MSGraph to tell you the link source of data in its data sheets.

It's a good idea to use & rather than + for string concatenation; VB/VBA may return
unexpected results otherwise. I don't see how that'd be a problem in this case but
it's a good habit to get into. For example, x + 10 would return a double, not a
string.

As to why not all of the charts are getting updated ... have you set a break point and
stepped through the code?

--------------------------------------------------------------------------------------
----------------------------------------------
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

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.
 
S

Steve Rindsberg

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.

Sorry ... you can't.
 
S

Steve Rindsberg

Ddwmoq said:
Oh! I won't do any office work at home.

<g> You're allowed to work at home. If Bill gives you any argument, tell him
we said it was OK and to buzz off and leave you alone.

But tell him how cheezed off we all are that he won't let us at the link source
in Graph. (It's actually almost worse than that: Graph *does* expose the fact
that there ARE links. It just --- I can just hear it going "Nyah nyah nyahhh!"
--- won't tell you where the links point.
 

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