Scripting Excel: fill column with items of list variable

[Mark Aalyson]

Having thrashed about with this for days, I would appreciate some help with
the syntax to to fill the cells of a designated column in a one-for-one
fashion with items from a list variable. As a bonus, it would be very neat
to set the size of the column range by script to match the number of items
in the list variable. This is for Excel 2004.

I've tried a bunch of stuff, but for starters:

set myList to {1, 2, 3, 4, 5, 6, 7, 8, 9}
set myCount to (count items of myList)
set n to myCount as integer
tell application "Microsoft Excel"
activate
set newBook to make new workbook
set myRange to range "B5:B13"
--I'd like to do something like "set myRange to ¬
range "B5:B(n)" but I get an error "myRange is not defined"
set value of myRange to items of myList
end tell
-- the result of this is that each cell of the designated range is filled
with the first item in the list, i.e., 1,1,1,1...

Thanks for any comments.

 

[Paul Berkowitz]

Having thrashed about with this for days, I would appreciate some help with
the syntax to to fill the cells of a designated column in a one-for-one
fashion with items from a list variable. As a bonus, it would be very neat
to set the size of the column range by script to match the number of items
in the list variable. This is for Excel 2004.

I've tried a bunch of stuff, but for starters:

set myList to {1, 2, 3, 4, 5, 6, 7, 8, 9}
set myCount to (count items of myList)
set n to myCount as integer

n already is an integer, no need to coerce it (and there's no "Dim" to
declare data types in AppleScript either). And myCount is already an integer
too. You do not need n at all. Nor do you need myCount!

tell application "Microsoft Excel"
activate
set newBook to make new workbook
set myRange to range "B5:B13"

Make sure you're talking about newBook:

set myRange to range "B5:B13" of worksheet 1 of newBook

--I'd like to do something like "set myRange to ¬
range "B5:B(n)" but I get an error "myRange is not defined"
set value of myRange to items of myList

If you read up in the Excel AppleScript Reference you'll see that value of a
range is a list of lists, not just a simple list. A simple list will coerce
implicitly if you're setting the value of a row (or a single cell, if one
item only), but for a column or any 2-dimensional range you really do need a
list of lists e.g.

{{1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}}

You can type it all out, like I just did (very tedious), or do it this way:

set myList to {1, 2, 3, 4, 5, 6, 7, 8, 9}
repeat with i from 1 to (count myList)
set item i of myList to {item i of myList}
end repeat

myList
--> {{1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}}

Also 'items of myList' is just the same as 'myList' - redundant.

end tell
-- the result of this is that each cell of the designated range is filled
with the first item in the list, i.e., 1,1,1,1...

Thanks for any comments.

So here you are:



set myList to {1, 2, 3, 4, 5, 6, 7, 8, 9}
repeat with i from 1 to (count myList)
set item i of myList to {item i of myList}
end repeat

tell application "Microsoft Excel"
activate
set newBook to make new workbook
set myRange to range "B5:B13" of worksheet 1 of newBook
set value of myRange to myList
end tell

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Paul Berkowitz]

If you read up in the Excel AppleScript Reference you'll see that value of a
range is a list of lists, not just a simple list. A simple list will coerce
implicitly if you're setting the value of a row (or a single cell, if one item
only), but for a column or any 2-dimensional range you really do need a list
of lists e.g.

{{1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}}

The internal sublists represent rows of the range: that's precisely why a
column needs single-item sublists. A range three columns wide and four rows
deep is filled this way:

set value of range "A1:C4" of active sheet to {{11, 21, 31}, {21, 22,
23}, {31, 32, 33}, {41, 42, 43}}

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Mark Aalyson]

Thanks for your instruction, Paul. This will advance me miles in my Excel
scripting. Lists of lists is a concept that always snags me. I had tried the
repeat block like this:

repeat with i in myrange
set (value of first cell of myrange where its value is {""}) to
first item of myList
set myList to rest of myList
end repeat

With the intention of peeling the first items from the list and inserting
them into each cell.

Thanks again.

 

[Mark Aalyson]

Paul,

I wanted to follow your suggestion and read through the EAR on this topic. I
couldn't find any discussion of the value of a range. I looked through the
reference manual for Excel 2004. Can you point me to a page?

 

[Hilena Hailu [MSFT]]

Hi Mark,

You can read about the Range class on page 328. There is also an example of
what you wanted to do on page 50.

Hope this helps!

 

[Paul Berkowitz]

No, Hilena, that example on p. 50 would _not_ help, it would only confuse
Mark further. It's a poor example - precisely what NOT to do. It should be
fixed. And p. 328 doesn't help either. The correct description is only on p.
336, but without any examples.

The example on p. 50:

set theList to {"Name","Full Name","Path","Installed"}
set value of range "A11" to theList

only works because range "A11" is a single row. That's what allows the
simple list to work. Properly speaking, it should really be

set theList to {{"Name","Full Name","Path","Installed"}}

or else:

set theList to {"Name","Full Name","Path","Installed"}
set value of range "A11" to {theList}

similar to my previous example. Excel is smart about coercing the simple
list to a single-item list of list when you're setting the value of a row,
so that's why this example works. But it's a VERY BAD example, since it;s
demonstrating a silent coercion of the wrong syntax that only works for a
row, not other ranges. Doing that is precisely what errored for Mark!

The WHOLE POINT was that Mark was trying to set the value of a COLUMN. As I
already said, a few times, you then really have to use a list of lists,
where each row's value is a sublist within the outer list. Mark's error was
that he tried to use a simple list, just like this example on p. 50, and IT
DOES NOT WORK FOR A COLUMN, nor for any range other than a row.

Since p. 50 offers the only example, that's exactly what people will try,
and they will continue to be puzzled and get things wrong, because the
example only works for the very special case of setting a single row,

Please read my previous post. As I pointed out there, to set the value of a
range, you need to set a list of lists, For Mark's column, that would be:

{{1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}}

For a range three columns wide and four rows deep:

{{11, 21, 31}, {21,22, 23}, {31, 32, 33}, {41, 42, 43}}

For a row, this will work, as it should:

{{"Name","Full Name","Path","Installed"}}

even though you can get away with

{"Name","Full Name","Path","Installed"}

since Excel will silently coerce that for you.

All anyone has to do is to GET the value of a range and you'll see for
yourself:

tell application "Microsoft Excel"
set theList to {"Name", "Full Name", "Path", "Installed"}
set value of range "A11" to theList
get value of range "A11"
end tell
--> {{"Name", "Full Name", "Path", "Installed"}}

See? Try it on a column, and you'll see how that looks too.


The description on p. 328 is not much better, because it doesn't spell out
that the value of a range is a list of lists. It just avoids the whole issue
by blurring it:

set value of range "A5" of active sheet to ¬
(get value of range "A1" of active sheet)

You can't actually tell what structure the value has here, although this
will copy the value correctly.


***But now go to p. 336.

The definition of the 'value' property of range, on p. 336, IS CORRECT:

"
If the range is a single cell, returns or sets the value of the specified
cell. If the cell is empty, value returns an empty string. If the range
object contains more than one cell, returns a list of values. Each internal
list within the outer list corresponds to a row of cell values. The value of
a single row returns a list containing a single internal list of cell
values.
"

See that

"Each internal list within the outer list corresponds to a row of cell
values."

That's correct. You can work out from that definition that there is a list
of lists. It's not a terribly clear explanation, though. And the final
sentence is correct as well: it explains that there's an outer list
containing a single internal list.

What's missing is some examples. There should be examples of getting and
setting the value of a column, a two-dimensional range, and a row.

And the example on p. 50 should be changed.



--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

From: "Hilena Hailu [MSFT]" <[email protected]>
Newsgroups: microsoft.public.mac.office.excel
Date: Tue, 28 Feb 2006 19:31:20 -0800
Subject: Re: Scripting Excel: fill column with items of list variable

Hi Mark,

You can read about the Range class on page 328. There is also an example of
what you wanted to do on page 50.

Hope this helps!

--
Hilena Hailu
Macintosh Business Unit
Microsoft Corp.

--------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.

Paul,

I wanted to follow your suggestion and read through the EAR on this topic. I
couldn't find any discussion of the value of a range. I looked through the
reference manual for Excel 2004. Can you point me to a page?