Unable to set the XValues property of the Series class

Darrell Hougen Fri, 18 Nov 2005 10:52:56 -0800

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.

BTW, I would be happy to supply the complete source and an example inputfile to whomever is interested.


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 (<FILE>)
   {
       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} = [EMAIL PROTECTED];
   $dataRange->{Value} = [EMAIL PROTECTED];

   # 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} = [EMAIL PROTECTED];
   # $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;


_______________________________________________
Perl-Win32-Users mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

Unable to set the XValues property of the Series class

Reply via email to