Setting axis data range on Excel chart using Win32::OLE

apple123 has asked for the wisdom of the Perl Monks concerning the following question:

I have written a program that uses Win32::OLE to add data to an Excel file and update a chart with the new data. The examples in Using Win32::OLE and Excel were invaluable, but I'm stuck on one last feature. I need to adjust the data ranges in the chart to include the newly-added data. I'm using the following code:

$sum_sheet->Range("B1:G2")->Copy();
$sum_sheet->ChartObjects(1)->Chart->SeriesCollection->Paste({CategoryL
+abels=>1, NewSeries=>0});
[download]

This correctly adjusts the data range for the chart Values, but the range for the Category (X) Axis Labels is incorrect as follows:
=(Summary!$B$1:$F$1,Summary!$B$1:$G$1)
when it should be:
=(Summary!$B$1:$G$1)
I have also tried using the SeriesCollection->Extend() method, but the results are similar. I've been banging my head on this wall for a while now. Any suggestions?

Thanks in advance,

Comment on Setting axis data range on Excel chart using Win32::OLE Select or Download Code

Replies are listed 'Best First'.
Re: Setting axis data range on Excel chart using Win32::OLE by Ponky (Curate) on Nov 17, 2006 at 06:22 UTC
I've also tried to get this type of thing working previously and given up in disgust! I would try just setting the X Axis values directly after the Paste: `$sum_sheet->ActiveChart->SeriesCollection(1)->{XValues} "='Summary!'$B +$1:$G$1";` [download]	[reply] [d/l]
Re: Setting axis data range on Excel chart using Win32::OLE by Unanimous Monk (Sexton) on Nov 17, 2006 at 15:29 UTC
If B1:G2 is the only data in the chart, you can use: `$sum_sheet->ChartObjects(1)->SetSourceData($sum_sheet->Range("B1:G2")) +;` [download]	[reply] [d/l]