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: Wed, 10 Nov 2004 12:22:21 -0800
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. If it doesn't exist, I'll see if it can be added. I
would have thought that 'currency' would be a sub-set of number values,
which are 'real' data type in AppleScript. It sounds as if there's
something wrong with 'currency' format values. I'll have to do some
comparative testing of real, integer and currency values, and then I'll
report any bugs I find. MS is committed to fixing AppleScript bugs for later
releases.
Note that you can text the class of the value you receive back (when you do
get a value). So if the class is date, you can get the short date. If the
class is real, you can coerce it 'as string'. You might first want to coerce
it to integer if it is a whole number:
set theValue to value of cell "B3" -- 3.0
if class of theValue if real then
try
set theValue to theValue as integer -- 3
end try -- if 3.75, it remains as is
set theValue to theValue as string -- "3" 0r "3.75"
else if class of theValue is date then
set theValue to short date string of theValue
else if theValue = missing value
-- some error routine here
else if class of theValue is Unicode text then
set theValue to theValue as string -- ?
--else if -- ??
end if
Even for text, since Excel returns Unicode text, you may need to coerce it
to string for some other apps. I answer to an earlier question of yours
string value of cell "B3"
will always get you a string (like 'text of cell' did in Excel X), but in
the case of dates you get that long verbose date & time, so this is not the
way to go.
--
Paul Berkowitz
> From: Nan Mossey <email@hidden>
> Date: Wed, 10 Nov 2004 13:54:34 -0500
> To: Paul Berkowitz <email@hidden>, AppleScript-Users
> <email@hidden>
> Subject: Re: Read a cell in Excel 2004
>
> Short date string was the answer! Thank you!
>
> If you ca help me with one last question, I'll be done with this and can get
> on to my real life. When I try to get the value of a cell whose number
> format is set to currency, my result shows up as "missing value". Any fixes
> for this? I'm much indebted.
>
> On 11/10/04 1:03 PM, "Paul Berkowitz" <email@hidden> wrote:
>
>> Yes.
>>
>> tell app "Microsoft Excel"
>> set Pubdate to value of cell "B3"
>> end tell
>>
>> set Pubdate to date string of Pubdate
>> --> "Thursday, November 4, 2004"
>>
>> --in OS 10.3:
>> set Pubdate to short date string of Pubdate
>> --> "11/4/04" -- in whatever format you have set in International System
>> Preferences/Format
>>
>>
>>
>> If you need the leading zeroes for month and day ("11/04/04"), you'll need
>> to set that format in International System Preferences/Format/Short Date.
>>
>> Or you can use a subroutine of your own (I can give you one), which will
>> also work in OS 10.2, to get a short date string of your choosing from
>> dates.
>
> --
> Nan Mossey
> Times Union Publications Manager
> 1 News Plaza
> Albany NY 12212
>
> 518-454-5704
>
>
> ========================================================
> This e-mail message is intended only for the personal
> use of the recipient(s) named above. If you are not
> an intended recipient, you may not review, copy or
> distribute this message.
>
> If you have received this communication in error,
> please notify the Albany Times Union Help Desk
> (email@hidden) immediately by e-mail and
> delete the original message.
> ========================================================
>
_______________________________________________
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