Learned Monastic Denizens

I've tried everything I know, consulted perlop on Range Operators, checked Perl in a Nutshell (Camel book at home) but this has me puzzled.

I'm trying to calculate the end column of a range of cols in an Excel s/sheet. I know where the range starts, and I know how many more cols I need to `add' to the start to get to the end. Ah hah! I thought, time for the `magic strings' and eventually came up with this snippet to convince me I was on track:-

#!/usr/bin/perl
use strict; 
use warnings;

#
#       test the magic strings
#

  my $col = 'p';
  my $limit = 120;
  for (-5..$limit) {
    $col++;
    print "$col,";
  }

[download]

Success! Works as advertised and I end up with column ref `el'. So I moved the same, so I thought, approach into the real program...


#!/usr/bin/perl
  use strict;
  use warnings;
  use Win32::OLE;     
  use Win32::OLE         qw(in with);
  use Win32::OLE::Const 'Microsoft Excel';
  use Win32::OLE::Variant;
  use Win32::OLE::NLS    qw(:LOCALE :DATE);
  #
</readmore>
  #    Based on `Using Win32::OLE and Excel - Tips and Tricks'
  #    by cacharbe - Mar 22, 2002 - node_id=153486.. with thanks 8-)
  #
<readmore>
  $Win32::OLE::Warn = 3; 

        my ($ex,        # this will be the ref to the Excel applicatio
+n
       $book,       # ref to the entire workbook
       $sheet,      # a particular worksheet
       $sheetcount) # count of all sheets in workbook
                  = undef;

#       Check that Excel is running - if not, start it
        eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
        die "Excel not installed" if $@;

        unless (defined $ex) {     # if $ex is undef here, need to sta
+rt Excel
          $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;
+})
          or die "Oops, cannot start Excel";
        }
    
        my $excelfile = "D:/murray/cut_test.xls";    

    # open existing workbook
    $book = $ex->Workbooks->Open($excelfile);
    $sheetcount = $book->Worksheets->Count();
    
#       open an output file to take csv formatted results
        open CURR, ">", "cut_extract.csv"
      or die "Can't open Extract for writing $!";

#       get the "one-off" key information from the top of the Total Su
+mmary sheet
    $sheet = $book->Worksheets("Total Summary");
        my $key_values  = $sheet->Range("M1:M8")->{Value};

        my ($project, $term, $local, $currency, $segment, $LOB, $first
+_month, $owner) = @{$key_values};

    for (@$key_values) {   
          print CURR "@$_,";                    # values out to .csv a
+s expected.
    }
    print CURR "\n";


    $sheet = $book->Worksheets("FTE");        # get focus on the FTE w
+orksheet

</readmore>
#       calculate the end column of months - month -6 starts in col P
#       so we need $term + 6 cols added to P.  The $range for Excel is
+ specified
#       as "CR:cr" where CR is the top, left cell of the range, and cr
+ the bottom
#       right. 
#
#       Determine the right-most column = 'P' + 6 + $term

    my $col = 'P';
    print "\$term is @{$term}\n";   # prints 120 as required
    for(-5..@{$term}) {             # but `..' seems to be in scalar c
+ontext!
      $col++; 
          print "$col,";
        }

        undef $book;
        undef $ex;
    close CURR;

[download]

But here I only end up with `W'... as if once -5 gets to zero the `..' flip-flops, then @{$term} is TRUE, and the iteration stops.

I'd appreciate some enlightenment in this branch of our theosophy... 8-)

... with humble thanks hagen

In reply to List or Scalar? by hagen

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.