Edit data in embedded Excel.Chart within PowerPoint using VBA

  • Thread starter Thread starter Ram Chepyala
  • Start date Start date
R

Ram Chepyala

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






- Show quoted text -
Click to expand...

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

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
 
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
 
Hi
I found a way of keeping the updates by using

oxl.save

Thanks all for your help

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

H
I found a way of keeping the updates by usin

oxl.sav

Thanks all for your hel

Regard
Ram

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx
 
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

H
I found a way of keeping the updates by usin

oxl.sav

Thanks all for your hel

Regard
Ram

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx
 
Hello Guys,

Need help. I am getting error while using this code - Run time error: Item object not found in the shapes collection. I don't know what is wrong in this code. Please help....

Dim oPPTFile As PowerPoint.Application
Set oPPTFile = ActivePresentation.Application
'oPPTFile.Slides(5).Select
oPPTFile.ActivePresentation.Slides(5).Select

Set oPPTShape = oPPTFile.ActivePresentation.Slides(5).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

Shyam Pillai said:
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
Click to expand...
 
Back
Top