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