Jump to content
Sign in to follow this  
HighlanderSword

Excel Multiple Column Sort

Recommended Posts

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 ?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by Juvigy

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×