Search code examples
excelapplescript

Applescript get all values of Excel column

I am trying to store all the values of an excel column in an array.

        set rangeDate to {value of range "A14:A100"}
        repeat with date in rangeDate
            if (date as string is equal to "01/01/2001") then
                log "It works"
            end if
        end repeat

In my Excel I do have an exact date of 01/01/2001 formatted in the specified columns. When I remove the range and it is just cell A14 (where the date is) it works. But when I include the range A14:A100 it doesn't work.

I am new to applescript, I guess that it doesn't store the values as array values and instead a string object? Any help would be appreciated

Solution

  • You have 4 issues :

    1) value of range should not be between {}, but between ()

    2) 'Date' is a reserved word in Applescript, so you should not use it as the variable in the loop. I replaced it with 'myDate'.

    3) instead of converting your date to string to compare with "01/01/2001", it is quicker to keep comparing 2 dates, and then, compare with the date "01/01/2001"

    4) I think it is a bug (at least with my Excel version), but the rangeDate variable is not a list of dates as expected, but for me a list of list : {{01/02/01},{02/02/01},………} Therefore, each member of 'rangeDate' is not a date, but a list made on one item which is a date ! I am not sure, but it could also be that range definition could be a list of ranges... So I am using item 1 of sub list.

    Anyway, script bellow is working :

    tell application "Microsoft Excel"
    activate
    tell active sheet of document 1
        set rangeDate to (value of range "A14:A100")
        repeat with mydate in rangeDate
            set TheDate to item 1 of mydate
            if TheDate = (date "lundi 1 janvier 2001 00:00:00") then
                log "It works"
            end if
        end repeat
    end tell
end tell