Re: beginner's Excel script (round2)
Re: beginner's Excel script (round2)
- Subject: Re: beginner's Excel script (round2)
- From: Paul Berkowitz <email@hidden>
- Date: Sat, 27 Mar 2004 16:19:33 -0800
On 3/27/04 12:18 PM, "Michael Bovee" <email@hidden> wrote:
>
<snippet from one of Pau's replies>
>
set theRange to Value of Range "R12C1:R2513C2" of ActiveSheet -- a
>
set Value of Range "R1C1:R2502C2" of ActiveSheet to theRange
>
-- without activating: of Worksheet 1 of Workbook "FILENAME.xls"
>
>
but when I tried this in various fiddling forms, I got an error that
>
AppleScript could not set theRange to an "integer" (maybe I was
>
supposed to define this selection as a curly-braced {list}?) and I
>
would get two columns of zeros where the 'selection' was supposed to be
>
pasted in...
OK, there are two issues here, I think.
1) One is that, for convenience, the Value of a Row is a simple list of
values:
{1, 2, 3, 4, 5}
whereas the Value of any other Range is a list of lists:
{{1,2,3,4,5}, {11, 12, 13, 14, 15}}
would be two rows of 5 cells each. That works for columns too:
{{1}, {11}, {21}, {31}}
would be a single column with four cells.
Since a Row is a subclass of Range, it should probably have had the more
complex format (list of list) in spite of the "inconvenience": perhaps a
revision of Excel's AppleScript will include that. Since you do need to
describe the destnation as a Range - or else as the Row of a Range - in that
case it may need the extra pair of braces as you guessed. You need to
experiment a bit to see if the Value of the first Range is already giving
you list of lists,:
{{1, 2, 3, 4, 5}}
or not. It's possible an adjustment might be needed, but I doubt it, Both
the getting and setting Values probably use the same format:
Value of Range "R12C1:R2513C2" of ActiveSheet
should already be giving you either a simple list, or else a list of lists.
Test it and find out.
2) There;s another issue, which is one of those Excel annoyances that needs
to be fixed. If you have any blank cells in the range you're getting, the
Value of that cell (or item in a Range list) is 0, the integer. Not 0.0, the
'real', which is what you get for the numerical value 0 in a worksheet. Just
plain 0. BUT, when you want to _saet_ a cell to have a blank value, you have
to set it to "", NOT to 0! Really stupid, and that needs to be fixed, but
that's how it is.
So - if theRange is a simple list (not a list of list), e.g.
{1,2,3,4,5}
you'd do this:
repeat with i from 1 to (count theRange)
set cellValue to item i of theRange
if cellValue is 0 then set item i of theRange to ""
end repeat
On the other hand if theRange is a list of list:
{{1,2,3,4,5}}
you'd do this:
set innerRange to item 1 of theRange
repeat with i from 1 to (count innerRange )
set cellValue to item i of innerRange
if cellValue is 0 then set item i of item 1 of theRange to ""
end repeat
--
Paul Berkowitz
[demime 0.98b removed an attachment of type application/pkcs7-signature which had a name of smime.p7s]
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.