Update PowerPoint chart by VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ddwmoq - 05 Oct 2006 07:23 GMT

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

Reply to this Message

David M. Marcovitz - 05 Oct 2006 15:37 GMT

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

Signature

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/

> 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
[quoted text clipped - 38 lines]
> MsgBox "The following charts were updated: " + vbCr + x
> End Sub

Reply to this Message

Steve Rindsberg - 05 Oct 2006 20:09 GMT

> 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
[quoted text clipped - 3 lines]
> 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
[quoted text clipped - 22 lines]
> Next
> Next

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

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

Reply to this Message

ddwmoq - 06 Oct 2006 02:29 GMT

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.

Reply to this Message

Steve Rindsberg - 06 Oct 2006 04:27 GMT

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

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

Reply to this Message

ddwmoq - 06 Oct 2006 05:07 GMT

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

Reply to this Message

Steve Rindsberg - 06 Oct 2006 16:04 GMT

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

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

Reply to this Message