Insert List into Excel Range

Scripting Forums AppleScript | Mac OS X
#1

Hi There,

I have a list that i am trying to insert into a range in Excel.

I’m using the method below to do this, but instead of one list item per cell in the range, I’m getting the whole list in a cell, then the whole list in the next cell, etc.

The list is thousands of items long, but I have tried adding a delay to counter this with no success.

does anyone have any ideas?


tell application "Microsoft Excel"
	activate
	set newBook to make new workbook
	activate newBook
	set name of active sheet to "totals"

-- list name is totalaction
	set endof to (count totalaction)
	set myrange to range ("A1 : A" & endof)
	set value of myrange to totalaction

end tell
#2

Ah I worked out the problem, apple text delimiters were not reset from an earlier part of the script!
The solution i posted above works now.

#3

does your totalaction have carriage returns separating the items?

if I add

set totalaction to {1,2,3,4,5,6,7}

to your script I get 7 as the value to 7 rows

if totalaction has returns

set totalaction to {1 & return, 2 & return, 3 & return, 4 & return, 5 & return, 6 & return, 7 & return}

I get 1 thru 7 entered into the rows.

Just thought that was an interesting result

#4

Ah it still isn’t working correctly…

The following code just puts the first item in the list in the column over and over:

set totals to {"ADAM", "DAVID", "BORIS", "HOWARD", "JANE", "HANNAH"}

tell application "Microsoft Excel"
	activate
	set newBook to make new workbook
	activate newBook
	set name of active sheet to "totals"
	
	-- list name is totals
	set endof to (count totals)
	set myrange to range ("A1 : A" & endof)
	set value of myrange to totals
	
end tell
#5

I’d like to avoid iterating through the list because the list will be thousands of lines long eventually…

Is there something obvious i am missing?

#6

The value of a multiple-row range in Excel is actually a nested list of lists, with the outer list representing the rows and the inner lists representing the columns. So something like this:

{{r1c1, r1c2, r1c3}, {r2c1, r2c2, r2c3}, ... }

Your one-dimensional list

{"ADAM", "DAVID", "BORIS", "HOWARD", "JANE", "HANNAH"}

represents the values of a single row

{r1c1, r1c2, r1c3, ...}

and therefore it tries to assign the same value to each row in your range, but since your range is only one column wide, you end up just getting the first item in the list repeated over and over.

Your list should be this instead:

{{"ADAM"}, {"DAVID"}, {"BORIS"}, {"HOWARD"}, {"JANE"}, {"HANNAH"}}