Setting and getting Excel cell value in Applescript

Question

I seem not to be able to access a cell or a range using column and row number in Excel via Applescript.

Minimal example:

tell application "Microsoft Excel"
    -- works
    log range (get address row 1, column 1) of sheet 1 of active workbook
    -- fails
    set r to (range (get address row 1, column 1) of sheet 1 of active workbook)
end tell

Why does

log range (get address row 1, column 1) of sheet 1 of active workbook

work while

set r to (range (get address row 1, column 1) of sheet 1 of active workbook)

fails?

Answer 1

The log command works because it is doing something very different than the set command is attempting to do.

The set command is trying to set a variable to the results of an invalid statement. The result is an error.

error "Microsoft Excel got an error: The object you are ¬
trying to access does not exist" number -1728 from range ¬
    {"$1:$1", column "$A:$A"} of sheet 1 of active workbook

If you remove the commands from in front of the statement you will get the same error.

(range {get address row 1, column 1} of sheet 1 of active workbook)

The key part is this below, which is not a valid range. Instead, it is an attempt to make a list containing two separate items (a string and a column) into a range object:

range {"$1:$1", column "$A:$A"}

What log is doing is more passive. This is from the Language Guide

The value to display. Expressions are evaluated but object specifiers are not resolved.

The object specifier in this case is the range but it cannot be resolved as the syntax is incorrect. Therefore, log shows the bits and pieces but set fails.

If you wish to have a single range that includes all of row 1 and all of column A, then this would work:

tell application "Microsoft Excel"
    set rowRange to range "1:1"
    set colRange to range "A:A"
    
    set r to union range1 rowRange range2 colRange
    --> range "[Workbook1]Sheet1!$1:$1,$A:$A" of application "Microsoft Excel"
    select r
end tell

and instead to get the single cell at the intersection you can do:

tell application "Microsoft Excel"
    set rowRange to range "1:1"
    set colRange to range "A:A"
    
    set r to intersection range1 rowRange range2 colRange
    select r
end tell

You could also mimic the syntax of the result:

tell application "Microsoft Excel"
    set r to range "[Workbook1]Sheet1!$1:$1,$A:$A"
end tell

-- OR

tell application "Microsoft Excel"
    tell active sheet of workbook 1 
        set r to range "$1:$1,$A:$A"        
    end tell
end tell