What's in the first row of the data? If you want all five columns to be
plotted as separate series, make sure you have no column headers. The
behavior you describe is expected if the top cells of the first 4
columns are blank and the top cell of the fifth is not. Is the top cell
of the 5th column used as the legend entry for that series?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

wrote:

I'm using perl and Win32::OLE but I'm wondering if there
are any known issues before I delve more into a perl solution.

The problem is as follows:

I have a data sheet with 5 columns of data called "$Sheet".
In Excel 2000 each of the columns ends up in a separate series
when creating a chart with the 5 columns in the datarange.

When switching from Excel 2000 to Excel 2002 the chart creation
takes the 5 column range and sets the first 4 columns as the
first series. So I only get 2 series :-(

Does anyone have any idea how to work around this
or why it's happening?
Any pointers to relevant documentation somwhere?
Or any other suggestions?


A small snip of the relevant code for context follows.
(Anyone used to VB probably understand it)

# My datarange
my $Range = $Sheet-Range('B2:F38');

# Create a new chart
my $num_of_Sheets = $Book-Worksheets-{Count};
my $chart = $Book-Charts-Add({After = $Book-Worksheets($num_of_Sheets)})
|| die Win32::OLE-LastError();

# Name the chart
$chart-{Name} = $Cdays[$dow-1];

$chart-SetSourceData({Source = $Range,
PlotBy = xlColumns
});

# Put Chart on a page of its own
$chart-Location({Where = xlLocationAsNewSheet });

$chart-SeriesCollection(1)-{AxisGroup} = xlPrimary;
$chart-SeriesCollection(1)-{ChartType} = xlColumnStacked;
$chart-SeriesCollection(1)-Fill-TwoColorGradient(1,1);
$chart-SeriesCollection(1)-Fill-ForeColor-{SchemeColor} = 10;
$chart-SeriesCollection(1)-Fill-BackColor-{SchemeColor} = 1;

$chart-SeriesCollection(2)-{AxisGroup} = xlPrimary;
$chart-SeriesCollection(2)-{ChartType} = xlColumnStacked;
$chart-SeriesCollection(2)-Fill-TwoColorGradient(1,1);
$chart-SeriesCollection(2)-Fill-ForeColor-{SchemeColor} = 3;
$chart-SeriesCollection(2)-Fill-BackColor-{SchemeColor} = 1;

# Crashes on the following line with
# Can't use an undefined value as a HASH reference at ..... file name and linenr
$chart-SeriesCollection(3)-{AxisGroup} = xlSecondary;
$chart-SeriesCollection(3)-{ChartType} = xlLineMarkers;
$chart-SeriesCollection(3)-{MarkerBackgroundColorIndex} = 7;
$chart-SeriesCollection(3)-{MarkerForegroundColorIndex} = 7;
$chart-SeriesCollection(3)-{MarkerStyle} = xlTriangle;
$chart-SeriesCollection(3)-Border-{Colorindex} = 7;
$chart-SeriesCollection(3)-Border-{Weight} = xlMedium;

SNIP REST..