How to loop over selected cells in Excel with Applescript?

Question

In VBA I can do something like this to loop through selected cells in Excel:

for each c in Selection
   ' do things
next

I am trying to do the same in AppleScript but I don't seem to get anywhere. I do get the current cell, but even when I do

set c to count of selection

the result is that c is set to 0.

The Excel applescript manual doesn't seem to help, nor googling.

Thanks

Answer 1

This is obviously an old question but I think it's a good one, and one of the comments raises the matter of complex selections. So here are a couple of ways you can approach this with Mac Excel (2011 in my case). There is no error handling but at the bottom is a script that will pre-populate a worksheet for demonstration purposes. Depending upon how large your selection is, it could crash.

First, a very basic thing you can do: count the selected cells. This should work with both simple and complex selections.

tell application "Microsoft Excel" to count of cells of selection

Single-area selection

Second, if you have a simple selection, then it's still not too verbose to do things with the cells. For testing here, it works with a 2x2 range.

tell application "Microsoft Excel"
    select range "D2:E3"
    set c to selection
    value of areas of c
    --> {{7.0, 21.0}, {8.0, 24.0}} -- note hierarchy
    
    set voop to {}
    set ab to count of cells in c
    repeat with x from 1 to ab
        set end of voop to value of cell x of c as integer
    end repeat
    
    voop
    --> {7, 21, 8, 24} -- note absence of hierarchy
end tell

Multiple-area selection

Third, here's a method that will work with either complex or simple selections. Again, the script at the bottom will populate with suitable example data.

areas of selection returns a list of selected ranges. It's always a list so even if you have but a single range (or even a single cell), you get a list. With that in mind, this loops through the list of selected ranges and then through each cell in each range. The script is bulkier than it needs to be because I've included some example things that you can do as well as making the selections and returning results.

tell application "Microsoft Excel"
    activate
    -- make complex selection
    set aran to range "A:B 3:4"
    set bran to range "D2:E3"
    set cran to range "G5:H7"
    select (union range1 aran range2 bran range3 cran)
    
    with timeout of 10 seconds
        set c to areas of selection
        --> {range "[Workbook1]Sheet1!$A$3:$B$4" of application "Microsoft Excel", range "[Workbook1]Sheet1!$D$2:$E$3" of application "Microsoft Excel", range "[Workbook1]Sheet1!$G$5:$H$7" of application "Microsoft Excel"}
        
        set voop to {} -- value
        set coop to {} -- cell address
        
        repeat with euRg in c -- each range area
            set ceuRg to cells of euRg -- hierarchical cell list -- critical step!
            repeat with xy in ceuRg -- each cell in range area
                -- do random stuff with each cell
                copy contents of xy to end of coop
                copy value of xy as integer to end of voop
                set value of xy to ((value of xy) / 2)
            end repeat
        end repeat
        
        -- do more random stuff
        set AppleScript's text item delimiters to space
        display dialog voop as text -- display original values
        --> 8 66 6 44 7 21 8 24 1 2 3 4 5 6
        coop -- flattened cell list
        --> {cell "$A$3" of application "Microsoft Excel", cell "$B$3" of application "Microsoft Excel", cell "$A$4" of application "Microsoft Excel", cell "$B$4" of application "Microsoft Excel", cell "$D$2" of application "Microsoft Excel", cell "$E$2" of application "Microsoft Excel", cell "$D$3" of application "Microsoft Excel", cell "$E$3" of application "Microsoft Excel", cell "$G$5" of application "Microsoft Excel", cell "$H$5" of application "Microsoft Excel", cell "$G$6" of application "Microsoft Excel", cell "$H$6" of application "Microsoft Excel", cell "$G$7" of application "Microsoft Excel", cell "$H$7" of application "Microsoft Excel"}
        
    end timeout
end tell

Pre-populate selections

tell application "Microsoft Excel"
    activate
    set value of range "A:B 3:4" to {{8, 66}, {6, 44}}
    set value of range "D2:E3" to {{7, 21}, {8, 24}}
    set value of range "G5:H7" to {{1, 2}, {3, 4}, {5, 6}}
end tell

Answer 2

You can try something like this:

tell application "Microsoft Excel"
    set range1 to range "A1:A5"
    set value of range1 to {{1.0}, {2.0}, {3.0}, {4.0}, {5.0}}
    set range2 to range "B1:B5"
    set value of range2 to {3}

    repeat with i from 1 to 5
        set formula of row i of column 3 to "=A" & i & "+B" & i
    end repeat
end tell

You can read more here

You can also try:

set cellCount to count of (cells of selection)

Answer 3

It turns out that you have to use "count large" to get the number of cells in the selection. Once you've gotten there, it's straightforward - like this:

tell application "Microsoft Excel"
    repeat with j from 1 to count large of selection
         -- do stuff with the cell
         set value of cell j of selection to "cell_" & j
    end repeat
end tell

In order to get there I had to do

tell application "Microsoft Excel"
   set c to properties of selection
end tell
return c

and then go through the list of properties until I found a promising one. This was a nice way to get to the property list. Maybe there's a faster way on the AppleScript editor, but I'm a command line person.