Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

OLE - updating a chart in an excel spreadsheet

by Rom399 (Initiate)
on Mar 17, 2004 at 04:21 UTC ( #337221=perlquestion: print w/ replies, xml ) Need Help??
Rom399 has asked for the wisdom of the Perl Monks concerning the following question:

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 }

Comment on OLE - updating a chart in an excel spreadsheet
Download Code
Re: OLE - updating a chart in an excel spreadsheet
by NetWallah (Abbot) on Mar 17, 2004 at 05:44 UTC
    You have not identified the error you are getting - but I suspect it is in the SetSourceData call. The SourceData "Source" parameter takes a RANGE object. It appears you are trying to assign it a string.

    My recommendation would be to use Named ranges. In the original chart, setup the source data to a named range. Then, in you code, you can simply change the range cells that the name points to, and you are done.

    Offense, like beauty, is in the eye of the beholder, and a fantasy.
    By guaranteeing freedom of expression, the First Amendment also guarntees offense.

      Most of the time i don't get a error or error message. I get a compile crash when it get's to the sourceData update.. Also tryed using this but more perl interpreter crashes :(
      ### Here we update the graphs sourcedata my $Chart1 = $RequestsSheet->ChartObjects("Chart 1"); my $sourceRange = "A1:A295,G1:G295"; my $Chart1Range = $DataSheet->Range($sourceRange); $Chart1->SetSourceData({Source =>$Chart1Range,PlotBy => xl +Columns});
Re: OLE - updating a chart in an excel spreadsheet
by guha (Priest) on Mar 17, 2004 at 10:05 UTC

    First I must say I agree with NetWallah about the importance of posting the errormessage AND the offending linenumber.

    So now I'm guessing that the line

    ### Create a refrence to @items and insert it into excel $Range->{Value} = [\@items];
    could be part of your troubles.

    #!perl -w use strict; use Data::Dumper; my @items= ('A','B','C'); print Dumper(\@items); print Dumper([\@items]); __OUTPUT__ $VAR1 = [ 'A', 'B', 'C' ]; $VAR1 = [ [ 'A', 'B', 'C' ] ];
    HTH
      Every thing up to here works, soon as i put in this bit of code i get the perl interpreter crash hence no error messages.
      ### 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(); ############### This line here blows everything up. $Chart1->SetSourceData({Source => '=Data!\$A\$2:\$A\$295'} +);
      I have tried to update the Chart1 sourceData this way as well also get the perl interpreter to crash. No error messages.
      ### Here we update the graphs sourcedata my $Chart1 = $RequestsSheet->ChartObjects("Chart 1"); my $sourceRange = "A1:A295,G1:G295"; my $Chart1Range = $DataSheet->Range($sourceRange); ############### This line here blows everything up. $Chart1->SetSourceData({Source =>$Chart1Range,PlotBy => xl +Columns});

        You don't need to escape the $ in a single quoted string.
        # This line here blows everything up. $Chart1->SetSourceData({Source =>'=Data!\$A\$2:\$A\$295'}

        However, that doesn't fix the problem. The syntax of the second example using the Excel range seems correct but doesn't work. I tried several variations with no success.

        --
        John.

        This works for me

        my $Chart1Range = $DataSheet->Range("A1:A295,G1:G295"); $Chart1->SetSourceData($Chart1Range,xlColumns);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://337221]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (7)
As of 2014-12-20 04:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (95 votes), past polls