Excel query... thoughts and ideas please....

Scripting Forums AppleScript | Mac OS X
,
#1

Hi there,
I’m currently in the process of adapting a script that I’d already written for pulling data out of Excel, concatenating it and using the resultant copy.

At present the script works with a fixed number of columns, I’ve included the code below:-

tell application "Microsoft Excel"
	set startRow to 4
	set endRow to 25
	set address_list1 to value of range ("A" & startRow & ":A" & endRow)
	set address_list2 to value of range ("B" & startRow & ":B" & endRow)
	set address_list3 to value of range ("C" & startRow & ":C" & endRow)
	set address_list4 to value of range ("D" & startRow & ":D" & endRow)
	set address_list5 to value of range ("E" & startRow & ":E" & endRow)
end tell

What I’m wanting to end up with is a procedure that allows me to pass in a variable number of columns along with the start and end row, something like this:-

on my getCopy{"A,B,C,D,E",4,25}

Passing parameters into the procedure isn’t a problem I’d just like people’s thoughts on the best way to handle the data from Excel.
I was thinking something along the lines of looping through the A,B,C,D,E and putting the result of each in item1, item2, item3 … of a list.

Is this the best way to do it or can anyone think of a better alternative?

Thanks in advance,

Nick

#2

Hi Nick,

something like this?


getCopy("A", "E", 4, 25)

on getCopy(rowStart, rowEnd, columnStart, columnEnd)
	tell application "Microsoft Excel"
		tell active sheet
			return (get value of range (rowStart & columnStart & ":" & rowEnd & columnEnd))
		end tell
	end tell
end getCopy
#3

Thanks for the help Stefan. It’s nice and simple.

Regards,

Nick

#4

Following on from the last post I have encountered a problem with another handler.
If I call the handler twice only the second call is actually carried through e.g.

getCopy("A", "E", 4, 25)
getCopy("F", "H", 4, 25)

Please can someone tell me why this might be. By the way, the handler in question is not the one posted earlier it’s a much larger one that pulls text out of Excel, concatenates it and then get’s imported into a text box in Quark.

Thanks in advance,

Regards,

Nick

#5

Of course the first result get lost, unless you assign it to a variable like

set value1 to getCopy("A", "E", 4, 25)
set value2 to getCopy("F", "H", 4, 25)
#6

Yeah, sorry Stefan. I’d used your example but with hindsight it was probably misleading.

Nick