I'll bet there is a clever perl (ie 6 characters) way to do this, but
I've done it like this...
sub toLetter {
my $leftLetter = int($_[0]/26);
my $rightLetter = ($_[0] % 26) + 64;
if ($leftLetter>0) {
$leftLetter += 64;
return chr($leftLetter).chr($rightLetter);
} else {
return chr($rightLetter);
}
}
On Thu, 26 Aug 2004 08:46:13 -0400, DiGregorio, Dave
<[EMAIL PROTECTED]> wrote:
> So that gives me the ASCII Value. But I am looking for the 1-for-1 value.
> A = 1
> B = 2
> C = 3
> .....
> AA = 27
> AB = 28
>
> As the columns in Excel is are labeled
>
> So I have a Value at max row AN which is 40 Columns in the Excel Worksheet.
> Using the below method gives me decimal value 104 which is 'H' and I do pull
> this value. Any Ideas??
>
> Thanks
>
> d
>
>
>
> -----Original Message-----
> From: Colm Gallagher [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 26, 2004 2:55 AM
> To: DiGregorio, Dave
> Cc: perl_help
> Subject: Re: Help With OLE
>
> Hi David,
>
> First post to this list, so hi folks, pleasure to meet you all.
>
> To get the text (i..e showing currency symbols etc.) out of a field
> instead of the numeric value, use the Text property instead of Value
> property.
>
> sub toLetter {
> return chr(64+$_[0]);
> }
> $ReadData = $ReadSheet->Range(&toLetter($LastCol).'1')->{Value};
> print "$ReadData\n";
> my $ReadData = $ReadSheet->Range(&toLetter($LastCol).'1')->{Text};
> print "$ReadData\n";
>
> On another note, I couldn't get your sample code to work because
> $LastCol was returning as a number, not a column letter reference, so
> I knocked together a quick and dirty converter - you may need to
> expand this slightly to handle AA etc, or post back to let me know how
> you do cell referencing.
>
> I learned from your code how to use named parameters with OLE, so thanks!
> i.e. SearchDirection=>xlPrevious,
>
> --
> Colm Gallagher
>
> On Wed, 25 Aug 2004 15:41:00 -0400, DiGregorio, Dave
> <[EMAIL PROTECTED]> wrote:
> > Hey All,
> >
> > So I am building this app that parses and Excel spreadsheet for
> > a value based on the last column in the file. But, I seam to return the
> > Numeric value as opposed to the Alpha Value. Is there an easy way to get
> > around this??
> >
> > $LastCol = $ReadSheet->UsedRange->Find({What=>"*",
> >
> > SearchDirection=>xlPrevious,
> >
> > SearchOrder=>xlByColumns})->{Column};
> >
> > print "$LastCol\n" ;
> >
> > $ReadData = $ReadSheet->Range($LastCol.'4')->{Value} ;
> >
> > Thanks
> >
> > David R. DiGregorio
> >
> > 703 Rodi Road
> >
> > Pittsburgh, PA 15235
> >
> > p. 412-349-2440
> >
> > [EMAIL PROTECTED]
> >
> >
>
> --
> Colm
>
--
Colm
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
