Unable to set the XValues property of the Series class

Hi All,

I have tried umpteen ways of setting the XValues in an Excel
Spreadsheet from Perl 5.8.7 using Win32::OLE, and I cannot get anything
to work. I'm beginning to wonder if there is a bug either in Perl/OLE
or in Excel.

If I write

$series->{XValues} = 23;

I get a single label equal to 23.

If I write

$series->{XValues} = (23, 45);

I also get a single label equal to 23. There are 101 data values.

If I write

$series->{XValues} = [23, 45];

I get a single label equal to 45.

If I manually define a reference to a label array with 101 elements, I
get an error:

"Unable to set the XValues property of the Series class"

I also get that error if I try to feed it an array by other means.
Evidently, Excel won't handle an array of that size.

The alternative appears to be to use a range of cells on a sheet. But
in that case, I get another error:

Error: Win32::OLE(0.1702) error 0x80020005: "Type mismatch"
in PROPERTYPUTREF "XValues"

BTW, the data plots fine.

I am pulling my hair out here! Any help is greatly appreciated.

Thanks.

Darrell


# Create an Excel workbook.
my $excel = Win32::OLE->new("Excel.Application");
$excel->{Visible} = 1;
my $book = $excel->Workbooks->Add;
my $sheets = $book->Worksheets;

# Create a sheet for each file.
for (my $i = 0; $i < @dataFiles; $i++)
{
# Read a data file.
< SNIPPED >

$j = 0;
while ()
{
chomp;
push @labels, [$j];
# push @labels, \$j;
# push @labels, $j;
push @data, [$_];
$j++;
}

# Create a sheet with the data.
my $sheet = $sheets->Add;
my $nCells = @data;
my $labelRange = $sheet->Range("A1:A$nCells");
my $dataRange = $sheet->Range("B1:B$nCells");
$labelRange->{Value} = \@labels;
$dataRange->{Value} = \@data;

# Create a chart.
my $chart = $excel->Charts->Add;
$chart->{ChartType} = xlColumnClustered;
$chart->SetSourceData({Source => $dataRange, PlotBy => xlColumns});
# $chart->SeriesCollection->NewSeries();
# my $series = $chart->SeriesCollection(1);
my $series = $chart->SeriesCollection->Item(1);
print "name = ", $sheet->{'Name'}, "\n";
# $series->{HasErrorBars} = 1;

# $series->{XValues} = @labels;
# $series->{XValues} = \@labels;
# $series->{XValues} = $sheet->Range("A1:A$nCells")->{Value};
# $series->{XValues} = $sheet->Range("A1");
# $series->{XValues} = $sheet->Range("A1:A$nCells");
# $series->{XValues} = "='$sheet->{'Name'}'!A1:A$nCells";
print "='$sheet->{'Name'}'!\$A\$1:\$A\$$nCells\n";
$series->{XValues} = "='$sheet->{'Name'}'!\$A\$1:\$A\$$nCells";
# $series->{XValues} = $labelRange;
# $series->{XValues} = $labelRange->{Value};
# $series->{XValues} = "=A1:A$nCells";

my $error = Win32::OLE->LastError();
print "Error: $error\n";

$chart->{HasTitle} = 1;
$chart->ChartTitle->{Text} = $title;

Re: Unable to set the XValues property of the Series class

Evidently, no body knows the answer to this problem.

Darrell