Applescript: how to return Excel cell reference in $A$1 format

0
votes

I'm trying to write an Applescript that cycles through each cell in an Excel spreadsheet range and records both the cell value and the cell address in "$A$1" format. I can get the cell value, and I can get a complete reference to the cell location (e.g., cell "$A$2" of range "A2:B10" of sheet "Sheet1" of active workbook of application "Microsoft Excel"), but I don't know how to extract just the cell reference on its own (e.g., "$A$2").

set addressList to {}
tell application "Microsoft Excel"
    tell sheet "Sheet1" of active workbook
        set myRange to range "A2:B10"
        repeat with j from 1 to count of every cell in myRange
            set end of addressList to cell j of myRange
        end repeat
    end tell
end tell
addressList
1
excelapplescript

1 Answers

1
votes

Use the get address command

set addressList to {}
tell application "Microsoft Excel"
    tell sheet "Sheet1" of active workbook
        set myRange to range "A2:B10"
        repeat with j from 1 to count of every cell in myRange
            set end of addressList to get address of cell j of myRange
        end repeat
    end tell
end tell
addressList