I’m trying to write a dynamic custom sort routine but my script is failing if the current sort orientation is set to rows rather than columns. Does anybody have any idea how I can clear the current sort using the clear sort fields command and reset the orientation of the sort from rows to columns?
The below script works if I manually clear all of the sort fields in the active worksheet and change the sort options to ‘Sort top to bottom’ rather than ‘Sort left to right’. If I don’t do this my script errors:
tell application "/Applications/Microsoft Office 2011/Microsoft Excel.app"
set myref to active sheet of active workbook
set rowCount to count of rows of used range of myref
set columnCount to count of columns of used range of myref
set storeDataRow to 8
set SKUdataColumn to 12
set Sort_Object to sort object of myref
repeat with i from SKUdataColumn to columnCount --> I need to dynamically add the columns to be sorted as the number of columns will vary
set sortStart to get address of cell storeDataRow of column i
set sortEnd to get address of cell rowCount of column i
set mySortRange to sortStart & ":" & sortEnd
add sortfield (sortfields of Sort_Object) key range (get address of column i of myref) order sort descending
end repeat
set sortStart to get address of cell storeDataRow of column 1
set sortEnd to get address of cell rowCount of column columnCount
set mySortRange to sortStart & ":" & sortEnd
log mySortRange
set sort range Sort_Object rng range mySortRange --> this is the range of cells that need to be sorted
apply sort Sort_Object
end tell
I think I’ve worked out how to do this by adding the below lines:
set sort orientation of Sort_Object to sort columns
set sortfieldList to every sortfield of Sort_Object
repeat count of sortfieldList times
delete sortfield item 1 of sortfieldList
end repeat
It’s taken me a while to work this out but hopefully it may help others that have similar problem. This is my full code:
tell application "/Applications/Microsoft Office 2011/Microsoft Excel.app"
set myref to active sheet of active workbook
set rowCount to count of rows of used range of myref
set columnCount to count of columns of used range of myref
set storeDataRow to 8
set SKUdataColumn to 12
set Sort_Object to sort object of myref
set sort orientation of Sort_Object to sort columns
set sortfieldList to every sortfield of Sort_Object
repeat count of sortfieldList times
delete sortfield item 1 of sortfieldList
end repeat
repeat with i from SKUdataColumn to columnCount --> I need to dynamically add the columns to be sorted as the number of columns will vary
set sortStart to get address of cell storeDataRow of column i
set sortEnd to get address of cell rowCount of column i
set mySortRange to sortStart & ":" & sortEnd
add sortfield (sortfields of Sort_Object) key range (get address of column i of myref) order sort descending
end repeat
set sortStart to get address of cell storeDataRow of column 1
set sortEnd to get address of cell rowCount of column columnCount
set mySortRange to sortStart & ":" & sortEnd
log mySortRange
set sort range Sort_Object rng range mySortRange --> this is the range of cells that need to be sorted
apply sort Sort_Object
end tell