Win32::OLE Excel Last Column problem,

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}
}
[download]

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};
[download]

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

Comment on Win32::OLE Excel Last Column problem, Select or Download Code

Replies are listed 'Best First'.
Re: Win32::OLE Excel Last Column problem, by VSarkiss (Monsignor) on Oct 21, 2005 at 15:02 UTC
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. Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l] [select]
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` [download] Celebrate Intellectual Diversity	[reply] [d/l]
Re^2: Win32::OLE Excel Last Column problem, by VSarkiss (Monsignor) on Oct 21, 2005 at 15:08 UTC
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;` [download] Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l]
Re^2: Win32::OLE Excel Last Column problem, by blackadder (Hermit) on Oct 21, 2005 at 15:15 UTC
Do what mate!!!thats "I" and "V" letters Blackadder	[reply]