Win32::OLE Excel Last Column problem,

[Anonymous Monk]

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

[VSarkiss]

This really has to do with Excel and COM, not Perl, but nonetheless...

The method you want is Cells, not Range. The Range method uses "A1" style notation, whereas Cells uses "R1C1" -- in other words, it takes row and column numbers. Note that the order is different: in "A1" it's column/row, in "R1C1" it's row/column.

If you haven't done so already, take a look at brother cacharbe's excellent Tutorial, Using Win32::OLE and Excel - Tips and Tricks.

[InfiniteSilence]

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

[VSarkiss]

I can't tell if you're joking, but the "IV" in the OP's question isn't a Roman numeral: it's the 256th column in an Excel spreadsheet, starting with "A".

If it was a joke, then here's another way to get it:

my $lastcol = "A";
$lastcol++ for 1..255;

[blackadder]

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