Create a list that can be evaluated by the Excel command "Choose"

[MitchBVI]

I have a routine that repositions columns on an Excel worksheet. My problem is that the required order of the list can change and I cannot work out how to create the list so it can be processed.

The repeat loop is designed to create the list, if I leave that out the code works of course

The routine at present is:

set HeadOrg to {"symbol", "average_days_to_recovery", "payout_next_ex_date", "short_name", "dividend_annual_current", "dividend_policy_status", "dars_overall", "last_price", "dividend_yield_current"}
set LocOrder to {"," & "1" & "," & "4" & "," & "7" & "," & "2" & "," & "3" & "," & "6" & "," & "5" & "," & "9" & "," & "8"}
log LocOrder
set Orderfinal to {"symbol 1", "short_name 4", "dars_overall 7", "average_days_to_recovery 2", ¬
	"payout_next_ex_date 3", "dividend_policy_status 6", "dividend_annual_current 5", "dividend_yield_current 9", "last_price 8"}
set LocOrder to {}
repeat with cnt from 1 to count of HeadOrg
	set Head to item cnt of HeadOrg
	--log Head
	repeat with Cnt2 from 1 to count of HeadOrg
		--log cnt & " " & Head & " " & word 1 of item Cnt2 of Orderfinal
		--log Head is word 1 of item Cnt2 of Orderfinal
		if word 1 of item Cnt2 of Orderfinal is Head then
			--log cnt & " " & "\"" & Cnt2 & "\"" & " " & Head
			if cnt is 1 then
				--set end of LocOrder to "," & "\"" & Cnt2 & "\"" & ","
				set end of LocOrder to "," & "\"" & Cnt2 & "\""
				--log LocOrder & " " & Head
			else
				if cnt is not 9 then
					--set end of LocOrder to "\"" & Cnt2 & "\"" & ","
					set end of LocOrder to "\"" & Cnt2 & "\""
					--log LocOrder & " " & Head
					--end if
				else
					--set end of LocOrder to "," & "\"" & Cnt2 & "\"" & ","
					set end of LocOrder to "\"" & Cnt2 & "\""
				end if
			end if
			exit repeat
		end if
	end repeat
end repeat
set LocOrder to LocOrder
log LocOrder as list
tell application "Microsoft Excel"
	set usedRngAddr to get address of used range of active sheet
	set fml to "=CHOOSECOLS(" & usedRngAddr & LocOrder & ")"
	set newRng to evaluate name fml
	make new worksheet at after last sheet of active workbook
	set value of range usedRngAddr to newRng
end tell

This does not work as the original LocOrder has quotes around each column number the script above also has quotes. I have left in the lines without quotes.
The two lines below are log results for the defined variables and the result of the above code.
(,1,4,7,2,3,6,5,9,8)
(,“1”, “4”, “5”, “2”, “7”, “6”, “3”, “9”, “8”)

So I assume its something to do with the way I am creating the list.

[roosterboy]

OK, if I’m understanding you correctly, I think something like this should give you the desired results.

I started with a sheet of test data like this:

And ended up with a result like this on a new sheet:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

-- converts an AS list to a textual representation of an XL array
-- NB: only works correctly with string values
to convertASListToXLArray(theList)
	if class of theList is not list then set theList to {theList}
	set {saveTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {"\",\""}}
	return "{\"" & (theList as text) & "\"}"
	set AppleScript's text item delimiters to saveTID
end convertASListToXLArray

-- given a list of column names in the desired order, creates a new worksheet
-- with the columns in that order
to copyReorderedColumnsToNewSheet(usingOrder)
	-- first we take an AS list of the column names in the final order we want them
	-- and convert it to a text representation of an XL array
	set xlDesiredOrder to convertASListToXLArray(usingOrder)
	
	tell application "Microsoft Excel"
		tell active workbook
			tell active sheet
				-- get address of the used range so we can plug it into our formula
				set usedRng to (get address used range)
				-- we have to get an array of our column indices in the correct order
				-- we do this by taking an array of the column names in the order we want
				-- and using XMATCH to find the index of each in the first row of fromRng
				-- we then use the ARRAYTOTEXT function to convert the result array
				-- into text we can plug into our CHOOSECOLS function later
				-- =ARRAYTOTEXT(XMATCH({...},$1:$1),1)
				set fmlIndices to "=ARRAYTOTEXT(XMATCH(" & xlDesiredOrder & ",$1:$1),1)"
				set indices to evaluate name fmlIndices
				-- this results in "{1,4,7,2,3,6,5,9,8}"
				
				-- now we can use CHOOSECOLS on the used range and supply the 
				-- address of our used range and the array
				-- we just created for the column order we want
				-- CHOOSECOLS($A$1:$I$7,{1,4,7,2,3,6,5,9,8})
				set fmlCols to "=CHOOSECOLS(" & usedRng & "," & indices & ")"
				set cols to evaluate name fmlCols
			end tell
			
			-- create a new sheet
			set newSh to make new worksheet at after last sheet
			tell newSh
				-- and spit out the reordered columns to the same range
				-- on the new sheet where it was on the original sheet
				set value of range usedRng to cols
			end tell
		end tell
	end tell
end copyReorderedColumnsToNewSheet

on run
	set desiredOrder to {"symbol", ¬
		"short_name", ¬
		"dars_overall", ¬
		"average_days_to_recovery", ¬
		"payout_next_ex_date", ¬
		"dividend_policy_status", ¬
		"dividend_annual_current", ¬
		"dividend_yield_current", ¬
		"last_price"}
	
	copyReorderedColumnsToNewSheet(desiredOrder)
end run

This would have been a little bit easier using Excel’s LET function, but it choked on me when I tried to use it with more than 4 columns. Bummer.

I think this should be commented well enough to tell what’s going on. If not, let me know.

[MitchBVI]

Brilliant thank you very much. You say NB: only works correctly with string values is that why you use those to set the order rather than the way I was trying to do it with numbers I ask as I thought putting the numbers in quotes set them as strings, your way is better and easier to understand. If I have understood your code correctly the convertASListToXLArray solves the problem I had with building the array in the first place (not I am afraid to admit that I knew I was building an array I thought it was a list". I had thought if one changed the text delimiter’s that they should be set back before exiting is that no longer correct.

[roosterboy]

@MitchBVI

Brilliant thank you very much. You say NB: only works correctly with string values is that why you use those to set the order rather than the way I was trying to do it with numbers I ask as I thought putting the numbers in quotes set them as strings, your way is better and easier to understand.

The thing is, though, that you don’t want the numbers as strings if they are supposed to represent column indices. Excel wants them as numbers and then we put the entire array in a string to build our formula.

So "{1,4,7,2,3,6,5,9,8}" rather than {"1","4","7","2","3","6","5","9","8"}

To set the final order of the columns, you need to find the current index of each column so you can use those in the second parameter to CHOOSECOLS. We do that by using XMATCH to find each column’s index and this requires searching row $1:$1 for the text of each column header. That’s why we need an array of strings. We use the array of strings (i.e., column headers) to get an array of integers (i.e., column indices).

If I have understood your code correctly the convertASListToXLArray solves the problem I had with building the array in the first place (not I am afraid to admit that I knew I was building an array I thought it was a list". I had thought if one changed the text delimiter’s that they should be set back before exiting is that no longer correct.

Sorry, that’s a copy/paste artifact of restructuring my code before posting it. I should have removed the line that resets the text item delimiters once I put that code into its own handler because that line will never even get called.