If you plot the data by xlColumnClustered rather than xlColumns you get essentially the same graph (better arguably) and you get a different pretty colour for each column.

You can probably hack about with ActiveChart.Location Where:=xlLocationAsObject, Name:="DepOne" o get it in the same sheet although you'd need to re-position it afterwards.

Here's an amended version of your code. Works in Excel 97 on NT

use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

# Start Excel and create new workbook with a single sheet
use Win32::OLE qw(in valof with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);

my (@Names);

$Win32::OLE::Warn = 3;

push @Names, ['loy', 13];
push @Names, ['toy', 12];
push @Names, ['mccoy', 05];
push @Names, ['roy', 47];
push @Names, ['loy', 24];
push @Names, ['toy', 05];
push @Names, ['joy', 24];

print "Start Excel\n";
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 1;
$Excel->{SheetsInNewWorkbook} = 1;
my $Book  = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
$Sheet->{Name} = 'DepOne';

my $Range = $Sheet->Range("A1:B1");
$Range->{Value} = [qw(Name Hours)];
$Range->Font->{Bold} = 1;

print "Add data\n";
$Range = $Sheet->Range(sprintf "A2:B%d", 2+$#Names);
$Range->{Value} = \@Names;

print "Create chart\n";
#$Sheet->Range("A:B")->Select;
$Sheet->Range("A1")->Activate;
$Sheet->Range("A1")->CurrentRegion->Select;
my $Chart = $Book->Charts->Add;
$Chart->{ChartType} = xlColumnClustered;
$Chart->SetSourceData({Source =>$Sheet->Range("A1")->CurrentRegion, Pl
+otBy=>xlRows});


$Chart->Location(xlLocationAsObject, $Sheet->{Name});
# Excel bug: old $Chart has become invalid now!
$Chart = $Excel->ActiveChart;

# Add title, remove legend
with($Chart, HasLegend => 0, HasTitle => 1);
$Chart->ChartTitle->Characters->{Text} = "Department One";
$Chart->Axes(xlCategory, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlCategory, xlPrimary)->AxisTitle->Characters->{Text} = "
+Names";
$Chart->Axes(xlValue, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlValue, xlPrimary)->AxisTitle->Characters->{Text} = "Hou
+rs";

# Fat candles with only 5% gaps
$Chart->ChartGroups(1)->{GapWidth} = 5;

sub RGB {
    my ($red,$green,$blue) = @_;
    return $red | ($green<<8) | ($blue<<16);
}

# White background with a solid border
$Chart->PlotArea->Border->{LineStyle} = xlContinuous;
$Chart->PlotArea->Border->{Color} = RGB(0,0,0);
$Chart->PlotArea->Interior->{Color} = RGB(255,255,255);

# Add 1 hour moving average of the Close series
my $MovAvg = $Chart->SeriesCollection(4)->Trendlines
             ->Add({Type => xlMovingAvg, Period => 4});
$MovAvg->Border->{Color} = RGB(255,0,0);

#$Book->SaveAs('somefile.xls');
#$Book->Close;
[download]

HTH - Mark

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.