CODEWITHSUNDEEP
excelmacosapplescript
codingguy3000
codingguy3000

Reputation: 2835

How can you reference named Excel columns using AppleScript?

Imagine you have an Excel 2010 spreadsheet named Customers.xlsx (It's easy if your try).

You have one worksheet named Customers.

The spreadsheet data looks like this:

CustomerId  Name      Email
----------  --------  ---------------
1001        Ken       [email protected]
2001        Jennifer  [email protected]
3001        Violet    [email protected]

I have a simple AppleScript that simply loops through this data and displays a dialog box for each row. 

set strExcelFileLocation to "Macintosh HD:Users:lowken:Documets:ExcelTest:Customers.xlsx"
set strWorkSheetName to "Customers"
set intCnt to 2
set intCustomerIdIndex to 1
set intNameIndex to 2
set intEmailIndex to 3

tell application "Microsoft Excel"
    activate
    --open workbook workbook file name strExcelFileLocation
    activate object worksheet strWorkSheetName

    set intLastRow to first row index of (get end (last cell of column 1) direction toward the top)

    tell active sheet to set myData to value of used range
    repeat
        set intCustomerId to item {intCustomerIdIndex} of item {intCnt} of myData
        set strEmail to item {intEmailIndex} of item {intCnt} of myData
        set strName to item {intNameIndex} of item {intCnt} of myData

        display dialog "Customer " & intCustomerId & " " & strName & " has an email address of " & strEmail 

        if intCnt = intLastRow then
            exit repeat
        end if
        set intCnt to (intCnt + 1)      
    end repeat  
end tell

Everything works great except I have one issue. I'm using integer index values to reference the columns. So I'm using 3 to reference the Email column.

What happens when I want to move Email to column 4? As it stands my AppleScript would break.

You can name Excel columns. This is done by highlighting the column (Column "C" for Email) and giving the column a name. In this example the name of Column "C" would be "Email".

Where I'm stuck is using a column name in my AppleScript.

I tried making the following change but it didn't work...

set strEmail to item {"Email"} of item {intCnt} of myData

is it possible to retrieve the column index via the column name?

Something like this:

set intEmailIndex to IndexOfExcelColumn("Email")

Can anyone show me how to work with column names in Excel. My ultimate goals is to be able to add a new column in the middle of my spreadsheet without breaking my AppleScript.

Thanks

Upvotes: 1

Views: 957

Answers (1)

jweaks
jweaks

Reputation: 3792

I'd suggest as per my comment that the simplest solution is to at the beginning of your script, loop through the columns and capture the index for each heading, and then you can proceed to use those indexes in the remainder of the script.

Upvotes: 1

Related Questions