i've a powerpoint presentation with a chart which contains data from an excel table.
I would like to edit this data via the powerpoint VBA editor..
how can i do this? i cant find a way to access the data of the excel table.
greets
-
Is your chart an Excel chart?Dr. belisarius– Dr. belisarius2010-12-30 09:35:19 +00:00Commented Dec 30, 2010 at 9:35
Add a comment
|
1 Answer
This code allows you to access an Excel WorkSheet embedded into a PowerPoint presentation.
Sub a()
Dim oSl As PowerPoint.Slide
Dim oSh As PowerPoint.Shape
Set oSl = ActivePresentation.Slides(1)
Set oSh = oSl.Shapes(1)
With oSh.OLEFormat.Object.Sheets(1)
.Range("A1").Value = .Range("A1").Value + 1
.Range("A2").Value = .Range("A2").Value - 1
End With
Set oSl = Nothing
Set oSh = Nothing
End Sub
If the graph is linked to the data you modify, probably it'll update automagically. If not, force a re-calc.
HTH!
Edit
With the following change it works in Office 2007:
With oSh.OLEFormat.Object.WorkSheets(1)
.Range("A1").Value = .Range("A1").Value + 1
.Range("A2").Value = .Range("A2").Value - 1
End With
9 Comments
darkdog
well that works only if i save the project as powerpoint 2003
darkdog
otherwise i get an error that says "OLEFormat(unknown member): Invalid request. This property only applies to OLE objects.
Dr. belisarius
Didn't test it in 2007 nor 2010. I almost always work in 2003 mode for compatibility with my clients' machines :(. Is that Ok?
darkdog
it seems that i have no other option as using the compatibility mode :(
Dr. belisarius
@darkdog I'll research a little, and come back if find another way.
|