mitchems has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

I am trying to use a range selection like:

Range(Cells(1, 1), Cells(5, 3))

in VB, but perl throws up when I do something like:

my $Range = $Sheet->Range(Cells(1,1),Cells(5,3));

while

my $Range = $Sheet->Range("A1:E3");

works fine.

Anyone know how to return a range handle to a range selected with the Cells(x,y),Cells(z,k)?

I can select one cell fine, but after that it bombs.

Thanks in advance.

Mike

Replies are listed 'Best First'.
Re: OLE::WIN32 Excel Range Selection
by sheep (Chaplain) on Dec 31, 2003 at 17:20 UTC
    Hello there,
    What you might want to do is: 
    $Sheet->Range(
        $Sheet->Cells(1,1),
        $Sheet->Cells(5,3)
    );

    [download]

    -sheep
[reply]
[d/l]
      Sheep! You rock! Thanks, that worked fine.

      I am still trying to get used to the OLE syntax.

      Thanks again - go perlmonks!

      M.

[reply]
Re: OLE::WIN32 Excel Range Selection
by bassplayer (Monsignor) on Dec 31, 2003 at 16:56 UTC
[reply]
      No, I'm using Win32::OLE because I have to create graphs in excel and move them to powerpoint (apparently powerpoint is the only language my boss understands).

      What I am doing is creating a graph. The entire code is: 

      $data[0]='162|There is a Project Management office in place that monit
+ors, participates and reports on projects|7|0';
$data[1]='163|There is a PMO that provides policies/procedures/tools f
+or project planning/tracking, which are used regularly|0|0';
$data[2]='164|There is no PMO, however, formal policies/procedures/too
+ls exist are used regularly|46|0';
$data[3]='165|Formal policies/procedures/tools exist but are not used|
+0|0';
$data[4]='166|Ad hoc, no formal policies/procedures|46|1';

$qid=65;
$binst=10;

$cname='Client Name';

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
$Win32::OLE::Warn = 3;   


$Excel = Win32::OLE->new('Excel.Application') or die "oops\n"; 

my $Book = $Excel->Workbooks->Open("c:\\stage\\scratch.xls"); 

my $Sheet = $Book->Worksheets("data");

  # skip empty cells
  $row=1;
  $col=1;
  $x=0;
  $y=1;
  for (@data){
      $x++;
      ($id,$label,$value,$checked)=split(/\|/);
      $Sheet->Cells($row,$col)->{'Value'}=$label;
      $Sheet->Cells($row,$col+1)->{'Value'}=$value;
      $Sheet->Cells($row,$col+2)->{'Value'}=$checked;
      if($checked==1){
          $y=$x;    
      }
      $row++;
  }
my $Range = $Sheet->Range("A1:B5");
my $Chart = $Book->Charts->Add;
$Chart->ChartWizard($Range,xlPie,6,xlColumns,1,0,1,"Approach to Projec
+t Management");
$text=$Chart->SeriesCollection(1)->Points(1)->DataLabel->{Text};

$Chart->SeriesCollection(1)->Points($y)->DataLabel->{Text} = "$cname\n
+$text";

$Chart->Legend->{Position} = xlLegendPositionBottom;

$Book->Close;

      [download]

      It is a test for creating multiple graphs in Excel.

      Any ideas on the syntax?

[reply]
[d/l]
        Oh, and scratch.xls is just an empty spreadsheet with on sheet called "data" - it doesn't have any other sheets or graphs on it when it starts. M.
[reply]

Back to Seekers of Perl Wisdom