Important Things To Note
Numbers stored in Excel are always returned in double
format, even whole numbers (integers) or numbers displayed/stored as text (string). So, what appears as “1” in a cell regardless of the cell’s format is always returned as “1.0” regardless of the number or text format. To circumvent this, use the trim or replace characters subroutines.
Getting Data From A Column
This code shows how to read a column of cells and get their values into a list.
set theFile to "Path:To:File.xls" as string set rangeStart to "A1" as string set rangeEnd to "A10" as string tell application "Microsoft Excel" activate open workbook workbook file name TheFile set theRange to rangeStart & ":" & rangeEnd set AppleScript's text item delimiters to {return} set theList to (get value of range theRange as list) set AppleScript's text item delimiters to {""} end tell
Getting Data From A Single Cell
Same as above, only duplicate the cell for the startRange to endRange as well.
tell application "Microsoft Excel" set theRange to "A1:A1" set theValue to (get value of range theRange) -- OR set theCellValue to value of (cell ("A1")) as string end tell
Getting the number of rows dynamically
on GetItemCount() set RowNumber to 1 set theText to "-1" tell application "Microsoft Excel" repeat until theText is "" set RowNumber to RowNumber + 1 set theRange to RequestIDColumn & RowNumber & ":" & RequestIDColumn & 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
Set the background color of an individual cell
This one is non-obvious. We have to get the interior object
of the cell, and the color is specified by it’s index in the current color palette. There are no color constants and there is no way to set the color by RBG values.
tell application "Microsoft Excel" set theRange to range "A3:E3" set interiorObject to interior object of theRange set color index of interiorObject to 5 end tell