Excel Multiple Column Sort

[HighlanderSword]

Hello,

Im trying to use comm to do a 4 column sort.

Below is the code snippet

include <Excel.au3>

$sfilepath="c:\daily_em.xls"

$oExcel_to=_ExcelBookOpen($sFilePath,1)

$oExcel_to.rows("1:2").delete

$oExcel_to.Cells.Select

;remove merged cell option

$oExcel_to.selection.MergeCells = False

$oExcel_to.Range("A1:m13595").Sort ($oExcel_to.Range("G2"),2, $oExcel_to.Range("F2"),Default, 2, $oExcel_to.Range("E2"),default,2,$oExcel_to.Range("C2"),1)

On the last line of the code I get an error message from Autoit

: ==> The requested action with this object has failed.:

$oExcel_to.Range("A1:m13595").Sort ($oExcel_to.Range("G2"),2, $oExcel_to.Range("F2"),Default, 2, $oExcel_to.Range("E2"),default,2,$oExcel_to.Range("C2"),1)

$oExcel_to.Range("A1:m13595").Sort ($oExcel_to.Range("G2"),2, $oExcel_to.Range("F2"),Default, 2, $oExcel_to.Range("E2"),default,2,$oExcel_to.Range("C2"),1)^ ERROR

What Im tryimng to do is sort the data in the range of a1:m13595

and apply a 4 level key sort using columns G2,F2,E2,C2

Im sorting on the first 3 keys ascending and then on the last key descending.

Any Ides what i'm missing here ?

[RobGuy]

I believe that the Excel.Application's Range object's Sort method supports only 3 keys internally:

[from the Object Browser's definition] Function Sort([Key1], [Order1 As XlSortOrder = xlAscending], [Key2], [Type], [Order2 As XlSortOrder = xlAscending], [Key3], [Order3 As XlSortOrder = xlAscending], [Header As XlYesNoGuess = xlNo], [OrderCustom], [MatchCase], [Orientation As XlSortOrientation = xlSortRows], [sortMethod As XlSortMethod = xlPinYin], [DataOption1 As XlSortDataOption = xlSortNormal], [DataOption2 As XlSortDataOption = xlSortNormal], [DataOption3 As XlSortDataOption = xlSortNormal])

A four key sort can be accomplished, I believe, utilizing the Sort object. In VBA it would look like:

Sub do_sort()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Range("G2:G13595"), xlSortOnValues, xlAscending
        .SortFields.Add Range("F2:F13595"), xlSortOnValues, xlAscending
        .SortFields.Add Range("E2:E13595"), xlSortOnValues, xlAscending
        .SortFields.Add Range("C2:C13595"), xlSortOnValues, xlDescending
        .SetRange Range("A1:M13595")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

... and in AutoIt we would have something like:

Opt("MustDeclareVars", 1)
Const $xlSortOnValues = 0, $xlAscending = 1, $xlDescending = 2, $xlYes = 1, $xlTopToBottom = 1, $xlPinYin = 1
Local $oExcel, $oSheet
;Get the current open excel application
$oExcel = ObjGet("", "Excel.Application")
;get first sheet
$oSheet = $oExcel.ActiveWorkbook.Worksheets("Sheet1")
;Make sure no existing SortFields interfere by clearing the SortFields
$oSheet.Sort.SortFields.Clear
;Add our desired sort parameters
$oSheet.Sort.SortFields.Add($oSheet.Range("G2:G13595"), $xlSortOnValues, $xlAscending)
$oSheet.Sort.SortFields.Add($oSheet.Range("F2:F13595"), $xlSortOnValues, $xlAscending)
$oSheet.Sort.SortFields.Add($oSheet.Range("E2:E13595"), $xlSortOnValues, $xlAscending)
$oSheet.Sort.SortFields.Add($oSheet.Range("C2:C13595"), $xlSortOnValues, $xlDescending)
;Make sure we have the right range targeted and options set
$oSheet.Sort.SetRange($oSheet.Range("A1:M13595"))
$oSheet.Sort.Header = $xlYes
$oSheet.Sort.MatchCase = False
$oSheet.Sort.Orientation = $xlTopToBottom
$oSheet.Sort.SortMethod = $xlPinYin
;Then ready to apply the sort
$oSheet.Sort.Apply

If you utilize the Excel.au3 then the only constants that will still need declaration are xlSortOnValues abd xlPinYin.

[Juvigy]

Is it working for you ? I played a lot with this but it doesnt work - i dont get any errors - just the sort is not applied.

This works but it is only limited to 3 sort fields:

$oExcel.Application.Worksheets("Sheet1").Range("A1:C6").Sort($oExcel.Application.Worksheets("Sheet1").Range("A1"),1, _
 $oExcel.Application.Worksheets("Sheet1").Range("B1"),Default,2,$oExcel.Application.Worksheets("Sheet1").Range("C1"),1)

Edited January 19, 2012 by Juvigy

[JoHanatCent]

Tried this with 2007 and managed to sort on 5 columns.

#include <Excel.au3>
$xlSortOnValues = 0
$xlPinYin = 1
$sfilepath = @ScriptDir & "" & "test.xls"
$oExcel = _ExcelBookOpen($sfilepath, 1)
;$oExcel.rows("1:2").delete
;$oExcel.Cells.Select
;remove merged cell option
;$oExcel.selection.MergeCells = False
$oSheet = $oExcel.ActiveWorkbook.Worksheets("Sheet1")
$oSheet.Sort.SortFields.Clear
$oSheet.Sort.SortFields.Add($oSheet.Range("G:G"), $xlSortOnValues, $xlAscending)
$oSheet.Sort.SortFields.Add($oSheet.Range("F:F"), $xlSortOnValues, $xlAscending)
$oSheet.Sort.SortFields.Add($oSheet.Range("E:E"), $xlSortOnValues, $xlAscending)
$oSheet.Sort.SortFields.Add($oSheet.Range("C:C"), $xlSortOnValues, $xlDescending)
$oSheet.Sort.SortFields.Add($oSheet.Range("M:M"), $xlSortOnValues, $xlAscending)
$oSheet.Sort.SetRange($oSheet.Range("A1:M1525"))
$oSheet.Sort.Header = $xlYes
$oSheet.Sort.MatchCase = False
$oSheet.Sort.Orientation = $xlTopToBottom
$oSheet.Sort.SortMethod = $xlPinYin
$oSheet.Sort.Apply