HighlanderSword Posted January 18, 2012 Share Posted January 18, 2012 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 ? Link to comment Share on other sites More sharing options...
RobGuy Posted January 18, 2012 Share Posted January 18, 2012 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. Link to comment Share on other sites More sharing options...
Juvigy Posted January 19, 2012 Share Posted January 19, 2012 (edited) 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 Link to comment Share on other sites More sharing options...
JoHanatCent Posted January 21, 2012 Share Posted January 21, 2012 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now