Q:

Ruby WIN32OLE excel chart seriescollection values

I am trying to change the values of an Excel (actually PowerPoint) chart.
I tried doing this by passing an array but it doesn't seem to work.
Although as mentioned on this page it should work...: http://msdn.microsoft.com/en-us/library/office/ff746833.aspx
So how does my code looks like at the moment:
require 'win32ole'
mspp_app = WIN32OLE.new("Powerpoint.Application")
mspp     = mspp_app.Presentations.Open(pathToFile)

slide         = mspp.Slides(1)
shapes        = slide.shapes
chartshape    = shapes(3) #the chart happens to be shape n°3
chart         = chartshape.chart

# now get the seriescollection
sc  = chart.SeriesCollection
sc3 = sc.Item(3)

values = sc3.values #returns the current values as an array example: [1.0, 1.0, 5.0, 2.0]

# now set the values
sc3.values = [2.0, 2.0, 5.0, 1.0] # returns no error

# see if the values are set
values = sc3.values # returns an empty Array [] 

Anyone tried this before?

A:

For manipulating the diagram data you have to change the underlying worksheet:
ws = myChart.Chart.ChartData.Workbook.Worksheets(1)

ws.Range("A2").Value = "Coffee"
ws.Range("A3").Value = "Soda"
ws.Range("A4").Value = "Tea"
ws.Range("A5").Value = "Water"
ws.Range("B1").Value = "Amount"  # used as label for legend
ws.Range("B2").Value = "1000"
ws.Range("B3").Value = "2500"
ws.Range("B4").Value = "4000"
ws.Range("B5").Value = "3000"

It is important to change the SourceData-Range if dimension has changed. Take care of the different notion known from Excel: "=Tabelle1!A1:B5" instead of "A1:B5".
For english office version change "Tabelle1" to "Sheet1"
myChart.Chart.SetSourceData("=Tabelle1!A1:B5")
myChart.Chart.ChartData.Workbook.Close

Don't forget to close the worksheet afterwards.

