win32::OLE and SeriesCollection on a chart

[axroh]

Hi folks,

I'm trying to do something like this:

$Sheet->Range("A1:C1")->Select;
my $y = $Excel->Selection;
my $x = $Sheet->Range($y, $y->End(xlDown));
my $Chart = $Book->Charts->Add;
$Chart->{ChartType} = xlXYScatterSmooth;
$Chart->SetSourceData($x,xlColumns);
$Chart->Location(xlLocationAsObject,$Sheet->Name);
$Chart->SeriesCollection(1)->Select;
$Chart->SeriesCollection(1)->{AxisGroup} = xlPrimary;

Everything is working fine until I access the SeriesCollection
property. I keep getting:
Can't call method "Select" on an undefined value at <filename> line
<line_number>

Even if I disable that line (I really don't need to select it, this
was inherited from a VBA macro -- hence the selection), I get the same
error for the next line. It's almost as if SeriesCollection is not an
object for Chart.

My range of data (A:A)->(C:C) are just numbers I wish to plot.

Any help here would be greatly appreciated.

 

[axroh]

Hello again,

It took me days to figure this out, I was really hoping to have saved
some time by asking on this group

Basically, if you change the location of a chart:

$Chart->Location(xlLocationAsObject,$Sheet->Name);

You need to update the handle, since it is lost. Luckily, there is
'ActiveChart':

# STUPID EXCEL BUG - CAUSED ME HOURS OF FRUSTRATION
# Chart handle is lost when you move the location of
# the chart. Stupid.
$Chart = $Excel->ActiveChart;

And then all else works ok. Hope this helps someone.