Re: Read a cell in Excel 2004
Re: Read a cell in Excel 2004
- Subject: Re: Read a cell in Excel 2004
- From: Paul Berkowitz <email@hidden>
- Date: Thu, 11 Nov 2004 00:12:19 -0800
On 11/10/04 10:03 PM, "David Wignall" <email@hidden> wrote:
> on 11/11/2004 9:22 AM, Paul Berkowitz at email@hidden wrote:
>
>> At first glance, I don't see anything that returns the type of format of the
>> cell values. I'm pretty sure that something like that _should_ be available,
>> but I don't know where.
>
> number format of range ("A1")
Right. Thank. Oddly, I just learned about this in VBA earlier today. What I
meant by the above sentence was that I expected that there would be a
property that returned whether the value of a particular cell happened to be
text, number, date or whatever. Instead it looks like you'd check for the
class of the 'value' (Unicode text, real, or date), and if 'real', then
check for the number format. If it's just a plain old number, where it
appears as an integer in Excel and as a real in AppleScript, the number
format is "General", as the Format/Cell also indicates.
E.g. It appears as 8000:
value of cell "D2"
--> 8000.0
number format of cell "D2"
--> "General"
If I set Format/Cell to "Number" then it appears as 8,000.00 in Excel and
value of cell "D2"
--> 8000.0
number format of cell "D2"
--> "0.00"
or to however many decimal places have been selected in Format/Cell. This is
good.
But if I change Format/Cell to "Currency", it appears as $8,000.00 in Excel
but, as Nan said:
value of cell "D2"
--> missing value
number format of cell "D2"
--> "$#,##0.00"
It seems to me that the value should still be 8000.00, not 'missing value'.
>
> -- "$#,##0.00"
>
> or
>
> -- "[$$-1409]#,##0.00"
>
> if I choose $ NZ for the currency symbol or
>
> -- "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"
>
> if I select the basic Accounting format. Working on the entirely valid
> assumption that it is the same as VBA then you can't pick up the 'category'
> of the format, as presented in the interface, but you can read/set the
> format of a range object; string or nowadays Unicode text. If you want to
> know the category then you have to parse the text. This was the advantage of
> 'text of range.' Shame that it's gone. It's still there in VBA:
> Range("A1").Text
It's not gone. It's 'string value' (as I said in an earlier message today):
string value of cell "D2"
--> "$8,000.00"
I've tested every possible setting. 'number format' always has a value (even
if you've entered text, not a number), as does 'string value' - which is
parseable, as you say. But 'value' retains the same real (8000.0) value in
all cases except when the format was set to Currency or Accounting. In those
two cases (including all the variants within them) it returns 'missing
value'. I'm sure that must be a bug - I'll report it now.
--
Paul Berkowitz
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Applescript-users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden