PERL OLE Troubles

[Hanousek, Roman]

 

Hi All 

I am bit lost with Excel Perl OLE Automation. I have a spreadsheet that
contains a number of sheets in it.

The data sheet contains all the data and the request sheet contain a
number of excel charts. The problem I have is that can't update the
sourceData in the Chart 1 on the requests sheet. 

 

Currently the sourceData range is =Data!$A$1:$A$291,Data!$G$1:$G$291
what I would like to do is just update it to
=Data!$A$1:$A$295,Data!$G$1:$G$295. Then update each of the other Charts
on that page with the same sourceData

     

 

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 excel

               $Range->{Value} = [EMAIL PROTECTED];

               $i++;

            }

            close(IN);

            

            ### Get the value that will be user to update all the graphs
SourceData with;

            $lastSourceData = $i -1; 

            print "lastSourceData: $lastSourceData \n";

            

            

            $RequestsSheet->Select();

            ### Here we update the graphs sourcedata

            

            

#### Everything work's till we get to here.

 

            my $Chart1 = $RequestsSheet->ChartObjects("Chart 1");

            $Chart1->Activate();

            

            $RequestsSheet->ChartObjects("Chart 1")->Activate();

            

            $Chart1->SetSourceData({Source => '=Data!\$A\$2:\$A\$295'});

            

           

          } ### Closing if($file

 

      } ### Closing the foreach    

}