Parsing Excel Range With Applescript

Scripting Forums AppleScript | Mac OS X
#1

Hey everyone.

I am having trouble parsing an excel range to create a usable string of text for the body of an email. I am trying to take the values in each of the cells of a range to create a string of text that is formatted as Such:

Name Email Phone Number

Jenna Jenna@gmail.com 824-236-4643

Meg Meg@gmail.com 744-236-4642

Lily Lily@gmail.com 624-236-4641

Problem: Right now when I create a variable to reference the value of this range it comes out as a long string of text

Applescript Code: set terry to value of range “D5:F7” of active sheet

What I get in email body with the variable terry above: JennaJenna@gmail.com824-236-4643MegMeg@gmail.com744-236-4642LilyLily@gmail.com624-236-4641

Basically, is there a way to parse through this range to add two spaces between each entry (Jenna & space & space & “jenna@gmail.com” & space & space & “824-236-4643” & return & “meg” & space & space …)

the variable comes out as a list in the result menu (AS: return terry): {{“Jenna”, “Jenna@gmail.com”, “824-236-4643”}, {“Meg”, “Meg@gmail.com”, “744-236-4642”}, {“Lily”, “Lily@gmail.com”, “624-236-4641”}}

I’ve been trying to use item #s to parse it, but that only separates by row, not by cell. So the same formatting problem, the lack of spaces, comes into play.

Does anyone have a workaround for this? Any help would be greatly appreciated.

#2

Here is one solution. To use, select your data and then run the script.


tell application "Microsoft Excel"
	activate
	set theRange to range of selection
	set rowCount to count of rows of selection
	set colCount to count of columns of selection
	set theResult to "" as text
	repeat with j from 2 to rowCount
		repeat with i from 1 to colCount
			set theResult to theResult & (value of row j of column i of selection) as text
			if i < colCount then
				set theResult to theResult & "  " as text
			else
				set theResult to theResult & return as text
			end if
		end repeat
	end repeat
	set the clipboard to theResult
end tell

This version puts together just the data and not the labels of the columns. To add the labels of the columns, change the “repeat with j from 2 to rowCount” to “repeat with j from 1 to rowCount”.

Hope this helps.

#3

You can speed up, by nearly a third, the previous solution I provided by simply removing the line “set theRange to range of selection”. That line is wrong anyway, but causes no error. In addition, “theRange” is not used anywhere else in the solution.

Having too much time on my hands, I tried using “set theRange to selection”, the correct way to get the range of the selection, and then replaced “selection” throughout the remainder of the script with “theRange”. Turns out using “theRange” is much slower than using “selection”.

#4

A huge step in the right direction! Thank you so much. It worked :slight_smile: