In AppleScript, you can get and set the value not only of an individual cell, but also of an entire row, column, or any range, via AppleScript lists. This is how it works: the value of a range is a list of lists, with the inner lists being the rows, and the items of the inner lists being the cells, as shown in the following example:
{{1, 2, 3}, {10, 20, 30}, {100, 200, 300}, {1000, 2000, 3000}}This represents a range with three columns and four rows. The top row contains
{1, 2, 3}, and so on.
A range consisting of a single row may be thought of as a single list
{1, 2, 3}. You can set the value of a three-columned row to that, if you want, and it will work.
If you ask Microsoft Excel
for the value, however, it will always return a list of lists
{{1, 2, 3}}, since a row is itself a range: a one-row range. That is its proper representation. You can set it that way, too.
A column's value is always represented like this:
{{1}, {10}, {100}, {1000}}, just like any range, but where the inner lists have just one item each.
A single cell's value is returned as just the value:
1, or
10, not as a list of lists. That is for convenience. And you can, and usually will, set it that way, too.
But you could set the value of a cell to
{{10}}, for example, instead of
10, and you would still see
10
in the cell.
Only the value property can be retrieved and set for a row, column, or range as a list of lists in this way. Not even the string value property can operate with multi-cell lists.
With all of this in mind, below are a couple of simple procedures brought over from Visual Basic for Applications (VBA).
Select the active cell's row or column
In VBA, you select the row or column of the active cell using the .EntireRow or .EntireColumn properties:
ActiveCell.EntireRow.Select
In AppleScript, that's:
select entire column of active cell
(select is in the Standard Suite.) It works perfectly.
Row height, column width, and autofit
While row height is set by the row height property in points, columns use the column width property to set the width in number of standard numeric characters (for example, the width of the zero,
0), which depends on the standard font and size set in Preferences. In VBA, you have the alternative of using the Autofit command on columns and rows to reduce the sizes to the minimum height and width required for the contents.
But there is a problem with the autofit command in AppleScript. The following macro uses all three:
Range("1:4").RowHeight = 20 'points
Range("A:C").ColumnWidth = 20 'characters
With Range("J5:Z43")
.Columns.AutoFit
.Rows.AutoFitThe first two statements translate perfectly to AppleScript, as you will see in a moment. There is nothing difficult about setting row height and column width.
To use the autofit command, which can be a great convenience, you need to loop it for each column separately. Nothing happens if you try it on columns as a plural, or every column.
That is manageable. However, the autofit command does not work for rows, not even a single row. The command messes up the columns instead, reducing the width of many of them and destroying any autofit you did for them, and doesn't do anything for the rows. That said, 99 percent of the time your rows are likely to be all the same height. If any need to be sized differently, you'll have to set their row height individually.
So, the way you have to convert the macro above is like this:
tell application "Microsoft Excel" set row height of range "1:4" to 20 -- points (rows) set column width of range "A:C" to 20 -- characters (columns) tell range "J5:Z43" of active sheet repeat with i from 1 to (count columns) autofit column i end repeat -- now see if here are any rows whose row height -- has to be adjusted, and do them separately end tell end tell
