Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Win32::OLE Excel Last Column problem,

by Anonymous Monk
on Oct 21, 2005 at 14:03 UTC ( [id://502000]=perlquestion: print w/replies, xml ) Need Help??

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

Hi



I need to obtain the used range in an Excel sheet. So I used the following subs, which they work fine.
sub Get_Last_Row { my ($Sheet) = @_; $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPreviou +s, SearchOrder=>xlByRows})->{ +Row} } sub Get_Last_Col { my ($Sheet) = @_; $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPreviou +s, SearchOrder=>xlByColumns}) +->{Column} }
In order for me to obtain the used range, I need to obtain the last used column and row in that xls sheet. However, The last Column's value is returned as an Interger! But this code
#my $LastRange = $LastCol.$LastRow; $LastRange = 'IV'.$LastRow; $ArrayData = $DataSheet-> Range("A1:$LastRange")->{Value};
requires the last column value to be in the format of Letter.Number!

So $LastCol will not work because its a number and I had to hard code 'IV' which I find it a bit silly.

my Question is :

1- how whould you guys solve this problem with minimal code?

2- What is the point in the Get_Last_col if it cannot be used directly?

Thanks

Replies are listed 'Best First'.
Re: Win32::OLE Excel Last Column problem,
by VSarkiss (Monsignor) on Oct 21, 2005 at 15:02 UTC
Re: Win32::OLE Excel Last Column problem,
by InfiniteSilence (Curate) on Oct 21, 2005 at 15:00 UTC
    This may seem like a silly answer but have you tried to use a perl module like Math::Roman?

    perl -e "use Math::Roman qw|roman|; $a = roman('I'); $a+= 1000; print $a->bstr();" MI

    Celebrate Intellectual Diversity

      Do what mate!!!thats "I" and "V" letters
      Blackadder

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others lurking in the Monastery: (2)
As of 2024-04-20 04:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found