2

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!

Yuval Karmi
  • 26,277
  • 39
  • 124
  • 175

1 Answers1

2

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
Philip Regan
  • 5,005
  • 2
  • 25
  • 39
  • At the same time, I think you need to spend some time with Applescript before diving in rb-appscript because I don't think much of it translates very well. – Philip Regan Aug 15 '11 at 14:42
  • thank you! though I see that this loops through 10 rows -- I'm looking to loop through the full range of rows. any ideas on how to achieve this? and I agree with your comment, though the guys at rb-appscript released this nifty little tool called ASTranslate that translates AppleScript to AppScript (either Ruby, Python, or Objective C). Can be found here: http://sourceforge.net/projects/appscript/files/ – Yuval Karmi Aug 15 '11 at 18:52
  • @yuval: I have updated my answer per your comments since the answer couldn't be answered as a comment. I still recommend spending some time in just Applescript to get a sense of context and intent of the rb-appscript project. – Philip Regan Aug 15 '11 at 18:58
  • This doesn't seem to work any more in Excel 2016 - it seems it doesn't like the way the Range is formatted. – Thomas Tempelmann Aug 01 '20 at 15:10