Hi i need a bit of help with some ole automation specificaly with updating a chart in an excel spreadsheet using perl.
What i am trying to do is update sourceData for "Chart 1" on the Requests sheet.
The sourceData for that chart currently has the values of '=Data!$A$1:$A$291,Data!$G$1:$G$291'
what i am trying to do change it to '=Data!$A$1:$A$295,Data!$G$1:$G$295' for example.
This Excel spreadsheet is already prebuilt contains a number of charts on the request,performance and errors pages.
Here is the code:
sub updateExcelCounters
{
my $excelToUpdate = shift;
my @dataFiles = @_;
###print "Excel: $excelToUpdate files; @dataFiles .\n";
my $Excel = Win32::OLE->new("Excel.Application");
$Excel->{Visible} = 1;
print "Excel: Opening Excel $excelToUpdate \n";
my $Book = $Excel->Workbooks->Open($excelToUpdate); # open Excel
+file
print "book : $Book\n";
my $DataSheet = $Book->Worksheets("Data");
my $RequestsSheet = $Book->Worksheets("Requests");
my $PerformanceSheet = $Book->Worksheets("Performance");
my $ErrorsSheet = $Book->Worksheets("Errors");
my $file;
foreach $file (@dataFiles)
{
if($file =~ /counters\.csv/)
{
my $line;
my $Range;
my @items;
my $valueRange;
my $lastSourceData;
my $i = 1;
$DataSheet->Activate();
open(IN,"<$file") || die "$!\n";
while($line = <IN>)
{
chomp $line;
@items = split/,/,$line;
$valueRange = "A" .$i . ":". "I" .$i;
$Range = $DataSheet->Range($valueRange);
#print "Range $Range\n";
### Create a refrence to @items and insert it into exce
+l
$Range->{Value} = [\@items];
$i++;
}
close(IN);
### Get the value that will be user to update all the grap
+hs SourceData with;
$lastSourceData = $i -1;
print "lastSourceData: $lastSourceData \n";
$RequestsSheet->Select();
### Here we update the graphs sourcedata
my $Chart1 = $RequestsSheet->ChartObjects("Chart 1");
$Chart1->Activate();
my $name = $Chart1->Name();
print "Chart1 Name: ", $name ,"\n";\
$RequestsSheet->ChartObjects("Chart 1")->Activate();
$Chart1->SetSourceData({Source => '=Data!\$A\$2:\$A\$295'}
+);
} ### Closing if($file
} ### Closing the foreach
}