Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Edit data in embedded Excel.Chart within PowerPoint using VBA

1,709 views
Skip to first unread message

Ram Chepyala

unread,
Apr 13, 2007, 9:54:02 AM4/13/07
to
Hi
I am updating the datasheet of embedded Excel.Chart with new values
and saving the powerpoint. When i open the saved PPT i can see the
chart updated, but when i double click on it the values change back to
orginal values and i am losing the updated chart. I am not sure where
i am doing it wrong.I am using the below code to edit datasheet
oPPTFile.Slides(33).Select
Set oPPTShape = oPPTFile.Slides(33).Shapes("Object")
Dim oxl As Excel.Workbook
Dim xchart As Excel.Chart
Set oxl = oPPTShape.OLEFormat.Object
Set xchart = oxl.Charts(1)
Set xlsheet = oxl.Worksheets(1)
xlsheet.Cells(2, 2) = -6
xlsheet.Cells(3, 2) = -7
xlsheet.Cells(2, 3) = 3
xlsheet.Cells(3, 3) = 8
xlsheet.Cells(2, 4) = -11
xlsheet.Cells(3, 4) = -1
xlsheet.Cells(2, 5) = 8
xlsheet.Cells(3, 5) = 4
Set xlsheet = Nothing
Set xchart = Nothing
Set oxl = Nothing
Normally with the MS graphs i use
Set oGraph = oPPTShape.OLEFormat.Object
oGraph.Application.DataSheet.Range("B1").Value =30
oGraph.update
do we have any sort of update for excel chart as well? Many thanks in
advance

Shyam Pillai

unread,
Apr 16, 2007, 10:17:20 AM4/16/07
to
Add oGraph.Application.Update at the end of the code. to ensure that your
changes are saved back into the presentation.

Regards,
Shyam Pillai

Toolbox: http://skp.mvps.org/toolbox

"Ram Chepyala" <chepy...@gmail.com> wrote in message
news:1176472442....@p77g2000hsh.googlegroups.com...

Ram Chepyala

unread,
Apr 16, 2007, 11:57:48 AM4/16/07
to
On Apr 16, 3:17 pm, "Shyam Pillai" <ShyamPil...@Gmail.com> wrote:
> Add oGraph.Application.Update at the end of the code. to ensure that your
> changes are saved back into the presentation.
>
> Regards,
> Shyam Pillai
>
> Toolbox:http://skp.mvps.org/toolbox
>
> "Ram Chepyala" <chepyal...@gmail.com> wrote in message

>
> news:1176472442....@p77g2000hsh.googlegroups.com...
>
>
>
> > Hi
> > I am updating the datasheet ofembeddedExcel.Chartwith new values
> > and saving thepowerpoint. When i open the saved PPT i can see the
> > advance- Hide quoted text -
>
> - Show quoted text -

Hi Shyam
I tried using oxl.update but it gives me an error saying the object
does not support

Steve Rindsberg

unread,
Apr 16, 2007, 3:23:41 PM4/16/07
to
In article <1176739067.9...@n76g2000hsh.googlegroups.com>, Ram

I made a few modifications that should be inconsequential but this version
works here; I can't get it to discard changes as you describe above:

Sub SlightModification()

' Set a reference to Excel 11 (I'm using Office 2003)
' Explicitly DIM the shape as a PPT shape
Dim oPPTShape As PowerPoint.Shape

' This shouldn't make any difference
' It gives me a ref to the shape w/o having to create and name
' shapes to duplicate your situation
Set oPPTShape = ActiveWindow.Selection.ShapeRange(1)

Dim oxl As Excel.Workbook
Dim xchart As Excel.Chart

' Explicitly DIM the xlsheet variable
Dim xlsheet As Excel.Worksheet

' the rest is copy/paste from your example, except I've changed the
' data a few times to verify that it's working
' the original data was a match to yours

Set oxl = oPPTShape.OLEFormat.Object
Set xchart = oxl.Charts(1)
Set xlsheet = oxl.Worksheets(1)

xlsheet.Cells(2, 2) = 123
xlsheet.Cells(3, 2) = 234
xlsheet.Cells(2, 3) = 345
xlsheet.Cells(3, 3) = 456


xlsheet.Cells(2, 4) = -11
xlsheet.Cells(3, 4) = -1
xlsheet.Cells(2, 5) = 8
xlsheet.Cells(3, 5) = 4


Set xlsheet = Nothing
Set xchart = Nothing
Set oxl = Nothing

End Sub

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


Ram Chepyala

unread,
Jun 5, 2007, 8:47:18 AM6/5/07
to
Hi Steve
Sorry for getting back to you very late
I was involved in another important work and could not get back to you
Iam still having the same problem; I am using Office 2003 and i have
explicitly declared all that suggested by you and the only difference
now is you suggested me to use this
Set oPPTShape = ActiveWindow.Selection.ShapeRange(1)

but i am using

Set oPPTShape = oPPTFile.Slides(2).Shapes("Object") as when i use the
top it reprots an error as not supported
now my changed code which doesn't work looks like this

Dim oPPTShape As PowerPoint.Shape
Set oPPTShape = oPPTFile.Slides(2).Shapes("Object")


Dim oxl As Excel.Workbook
Dim xchart As Excel.Chart

Dim xlsheet As Excel.Worksheet

Set oxl = oPPTShape.OLEFormat.Object
Set xchart = oxl.Charts(1)

'xchart.Application.PlotBy = xlColumns
'oGraph.Activate
Set xlsheet = oxl.Worksheets(1)
xlsheet.Cells(21, 7) = -20
xlsheet.Cells(22, 7) = -18
xlsheet.Cells(21, 8) = 26
xlsheet.Cells(22, 8) = 8
xlsheet.Cells(21, 9) = -23
xlsheet.Cells(22, 9) = -21
xlsheet.Cells(21, 10) = 25
xlsheet.Cells(22, 10) = 22
xlsheet.Cells(21, 11) = -3
xlsheet.Cells(22, 11) = 3
xlsheet.Cells(21, 12) = 25
xlsheet.Cells(22, 12) = 22
xlsheet.Cells(21, 13) = 25
xlsheet.Cells(22, 13) = 22
xlsheet.Cells(21, 14) = 25
xlsheet.Cells(22, 14) = 22

Set xlsheet = Nothing
Set xchart = Nothing
Set oxl = Nothing

Please let me know, if i am still doing it wrong.could you please send
me the power point to me as attachment

Many thanks in advance and onc eagin sorry for not getting back to you

Regards
Ram

Ram Chepyala

unread,
Jun 6, 2007, 6:01:16 AM6/6/07
to

Hi
I found a way of keeping the updates by using

oxl.save

Thanks all for your help

Regards
Ram

lauraritchey

unread,
Sep 30, 2009, 5:10:54 PM9/30/09
to
Ram,

Thanks so much for posting your solution to the problem! I had been trying (unsuccessfully) to find the answer on other forums, but it finally worked when I tried what you posted.

Thanks again!

Laura

Ram Chepyala wrote:

Re: Edit data in embedded Excel.Chart within PowerPoint using VBA
06-Jun-07

oxl.save

Regards
Ram

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorials/aspnet/8a2ea78b-f1e3-45b4-93ef-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

lauraritchey

unread,
Sep 30, 2009, 5:12:28 PM9/30/09
to
Ram,

Thanks for posting your solution to this problem! I had been searching (unsuccessfully) to find the answer to the same issue, and was unable to make it work until I tried your solution.

Thanks again!

-Laura

Ram Chepyala wrote:

Re: Edit data in embedded Excel.Chart within PowerPoint using VBA
06-Jun-07

Hi

oxl.save

Regards
Ram

EggHeadCafe - Software Developer Portal of Choice

Wyco

unread,
Dec 8, 2010, 7:38:35 AM12/8/10
to
Hello,

I've been trying to utilise the code(s) above, for a slightly similar reason.
What I'd like is that (when pressing a command button on the slide during a slideshow), the embedded chart would change its source to a different column and update. In other words, I'd like to create a dynamic chart of some sort.

the oxl.save doesn't really do the job I need, even if I include a forced refresh of the slide because the chart gets refreshed only when I click into it. the oxl.application.update gives me an error.
[version 2007]

here's the code I have at the moment. any help much appreciated!!
thnx

Option Explicit

Private Sub CommandButton1_Click()

Dim oPPtFile As PowerPoint.Presentation
Dim oPPTShape As PowerPoint.Shape
Dim oXL As Excel.Workbook
Dim oXLChart As Excel.Chart
Dim XLSheet As Excel.Worksheet


Dim PauseTime, Start

Set oPPtFile = ActivePresentation
Set oPPTShape = oPPtFile.Slides(1).Shapes(2)
Set oXL = oPPTShape.OLEFormat.Object
Set oXLChart = oXL.Charts(1)
Set XLSheet = oXL.Worksheets("Sheet1")


Dim z As Integer
Dim y As Integer
Dim NameRange As Range
Dim ValueRange As Range
Dim lSlideIndex As Long

oXLChart.SetSourceData Source:=XLSheet.Range(XLSheet.Cells(2, 1), XLSheet.Cells(7, 2))
oXLChart.ChartType = 51

y = 0
Do
y = y + 1
Loop While Len(XLSheet.Cells(y, 1)) > 0


Set NameRange = XLSheet.Range(XLSheet.Cells(2, 1), XLSheet.Cells(y - 1, 1))


PauseTime = 0.5 ' Set duration in seconds
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.

For z = 2 To WorksheetFunction.CountA(XLSheet.Rows(1)) + 1
Set ValueRange = XLSheet.Range(XLSheet.Cells(2, z), XLSheet.Cells(y - 1, z))
oXLChart.SeriesCollection(1).Name = XLSheet.Cells(1, z)
oXLChart.SetSourceData Source:=XLSheet.Range(NameRange, ValueRange)
oXL.Save
oXL.Application.Update 'this gives an error message
lSlideIndex = SlideShowWindows(1).View.CurrentShowPosition
SlideShowWindows(1).View.GotoSlide lSlideIndex
Next z
Loop
Set oXL = Nothing

End Sub


> On Friday, April 13, 2007 9:54 AM Ram Chepyala wrote:

> Hi


> I am updating the datasheet of embedded Excel.Chart with new values

> and saving the powerpoint. When i open the saved PPT i can see the


> chart updated, but when i double click on it the values change back to
> orginal values and i am losing the updated chart. I am not sure where

> i am doing it wrong.I am using the below code to edit datasheet

> oPPTFile.Slides(33).Select
> Set oPPTShape = oPPTFile.Slides(33).Shapes("Object")


> Dim oxl As Excel.Workbook
> Dim xchart As Excel.Chart

> Set oxl = oPPTShape.OLEFormat.Object
> Set xchart = oxl.Charts(1)

> Set xlsheet = oxl.Worksheets(1)
> xlsheet.Cells(2, 2) = -6
> xlsheet.Cells(3, 2) = -7
> xlsheet.Cells(2, 3) = 3
> xlsheet.Cells(3, 3) = 8
> xlsheet.Cells(2, 4) = -11
> xlsheet.Cells(3, 4) = -1
> xlsheet.Cells(2, 5) = 8
> xlsheet.Cells(3, 5) = 4

> Set xlsheet = Nothing
> Set xchart = Nothing
> Set oxl = Nothing

> Normally with the MS graphs i use
> Set oGraph = oPPTShape.OLEFormat.Object
> oGraph.Application.DataSheet.Range("B1").Value =30
> oGraph.update

> do we have any sort of update for excel chart as well? Many thanks in
> advance


>> On Monday, April 16, 2007 10:17 AM Shyam Pillai wrote:

>> Add oGraph.Application.Update at the end of the code. to ensure that your
>> changes are saved back into the presentation.
>>
>> Regards,
>> Shyam Pillai
>>
>> Toolbox: http://skp.mvps.org/toolbox


>>> On Monday, April 16, 2007 11:57 AM Ram Chepyala wrote:

>>> Hi Shyam
>>> I tried using oxl.update but it gives me an error saying the object
>>> does not support


>>>> On Wednesday, April 18, 2007 3:49 AM Steve Rindsberg wrote:

>>>> In article <1176739067.9...@n76g2000hsh.googlegroups.com>, Ram
>>>> Chepyala wrote:
>>>>
>>>> I made a few modifications that should be inconsequential but this version
>>>> works here; I can't get it to discard changes as you describe above:
>>>>
>>>> Sub SlightModification()
>>>>
>>>> ' Set a reference to Excel 11 (I'm using Office 2003)
>>>> ' Explicitly DIM the shape as a PPT shape
>>>> Dim oPPTShape As PowerPoint.Shape
>>>>
>>>> ' This shouldn't make any difference
>>>> ' It gives me a ref to the shape w/o having to create and name
>>>> ' shapes to duplicate your situation
>>>> Set oPPTShape = ActiveWindow.Selection.ShapeRange(1)
>>>>

>>>> Dim oxl As Excel.Workbook
>>>> Dim xchart As Excel.Chart
>>>>

>>>> ' Explicitly DIM the xlsheet variable
>>>> Dim xlsheet As Excel.Worksheet
>>>>
>>>> ' the rest is copy/paste from your example, except I've changed the
>>>> ' data a few times to verify that it's working
>>>> ' the original data was a match to yours
>>>>

>>>> Set oxl = oPPTShape.OLEFormat.Object
>>>> Set xchart = oxl.Charts(1)

>>>> Set xlsheet = oxl.Worksheets(1)
>>>> xlsheet.Cells(2, 2) = 123
>>>> xlsheet.Cells(3, 2) = 234
>>>> xlsheet.Cells(2, 3) = 345
>>>> xlsheet.Cells(3, 3) = 456
>>>> xlsheet.Cells(2, 4) = -11
>>>> xlsheet.Cells(3, 4) = -1
>>>> xlsheet.Cells(2, 5) = 8
>>>> xlsheet.Cells(3, 5) = 4
>>>>
>>>>

>>>> Set xlsheet = Nothing
>>>> Set xchart = Nothing
>>>> Set oxl = Nothing
>>>>

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


>>>>>> On Wednesday, June 06, 2007 6:01 AM Ram Chepyala wrote:

>>>>>> Hi
>>>>>> I found a way of keeping the updates by using
>>>>>>
>>>>>> oxl.save
>>>>>>
>>>>>> Thanks all for your help
>>>>>>
>>>>>> Regards
>>>>>> Ram


>>>>>>> On Wednesday, September 30, 2009 5:10 PM Laura Ritchey wrote:

>>>>>>> Ram,
>>>>>>>
>>>>>>> Thanks so much for posting your solution to the problem! I had been trying (unsuccessfully) to find the answer on other forums, but it finally worked when I tried what you posted.
>>>>>>>
>>>>>>> Thanks again!
>>>>>>>
>>>>>>> Laura
>>>>>>>
>>>>>>>
>>>>>>>

>>>>>>> Ram Chepyala wrote:
>>>>>>>
>>>>>>> Re: Edit data in embedded Excel.Chart within PowerPoint using VBA
>>>>>>> 06-Jun-07
>>>>>>>
>>>>>>> Hi
>>>>>>> I found a way of keeping the updates by using
>>>>>>>
>>>>>>> oxl.save
>>>>>>>
>>>>>>> Thanks all for your help
>>>>>>>
>>>>>>> Regards
>>>>>>> Ram
>>>>>>>
>>>>>>> EggHeadCafe - Software Developer Portal of Choice
>>>>>>> WPF DataGrid Custom Paging and Sorting
>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/8a2ea78b-f1e3-45b4-93ef-32b2d802ae17/wpf-datagrid-custom-pagin.aspx


>>>>>>>> Submitted via EggHeadCafe
>>>>>>>> Microsoft LINQ Query Samples For Beginners
>>>>>>>> http://www.eggheadcafe.com/training-topic-area/LINQ-Standard-Query-Operators/33/LINQ-Standard-Query-Operators.aspx

0 new messages