use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
$Win32::OLE::Warn = 0;

my $template = "D:/My Scripts/test_chart.xls";
my $outexcel = "D:/My Scripts/test_chart1.xls";

my $excel;
eval {$excel = Win32::OLE->GetActiveObject('Excel.Application')};
die "WARNING! Excel not installed" if $@;
unless (defined $excel) {
	$excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "ERROR, cannot start Excel";
}

$excel -> {Visible} = 1;
$excel->{DisplayAlerts} = 0;

die "Cannot find template $template\n" unless (-e $template);

my $workbook = $excel -> Workbooks -> Open($template);

my $range = "A1:C49";
my $sheet = $workbook -> Worksheets("Graph");
$sheet -> Activate();

my $chart = $sheet -> ChartObjects("Chart 1");
$chart -> Activate();
my $collection = $chart -> SeriesCollection(1); 
$collection -> Select(); # Can't call method "Select" on an undefined value
$chart->SetSourceData({Source => "$sheet", Range => "$range", PlotBy => "xlColumns"});
# $chart -> delete(); #This will work!

# save the spreadsheet
$workbook->SaveAs($outexcel);
undef $workbook;
undef $excel;
exit();