How to loop through all rows in Excel using Applescript?

Question

I'm trying to loop through all rows in Excel and run some command on each row, but I can't figure out how!

I tried initially doing that in rb-appscript (the Ruby wrapper for AppleScript) but decided that I'd be better off trying to get it working first in Applescript before adding another DSL. Any tips? (the rb-appscript version would be nice, too, though not required).

Thanks!

Answer 1

The coordinates of a range is a string that you can create dynamically. The simplest way to loop through your rows would be something like this:

repeat with thisRow from 1 to 10
    tell application "Microsoft Excel"
        set theRange to "A:" & thisRow & "Z:" & thisRow
        set theValue to (get value of range theRange) as list
        -- do something with the row's data
    end tell
end repeat

Update per comments: To get the number of rows needing calculation, I wrote a subroutine ages ago to help with this. Be sure you have some kind of "key" column that is consistently populated (in other words, there are no skipped cells). This currently takes into account a header row since all of my spreadsheets have them:

on GetItemCount(KeyColumn)
    set RowNumber to 1
    set theText to "cSyoyodylg" -- dummy value
    tell application "Microsoft Excel"
        repeat until theText is ""
            set RowNumber to RowNumber + 1
            set theRange to KeyColumn & RowNumber & ":" & KeyColumn & RowNumber
            set dataRange to range theRange of sheet 1
            set theText to (get value of range theRange)
        end repeat
    end tell
    set rowCount to RowNumber - 1
    return rowCount
end GetItemCount

To use simply do this: set lastRow to GetItemCount("A") of me

repeat with thisRow from 2 to lastRow + 1
    -- go attack Excel
end repeat