Using Excel's "Sort" Object & Methods

[CE101]

While waiting for advice from another posting (Sorting an Excel Worksheet - http://www.autoitscript.com/forum/index.php?showtopic=116619) I began researching a related subject --- how to change column widths in Excel.

And I came across a thread in Forums talking about a "method" by the name of "ColumnWidth". For example...

$oExcel.ActiveSheet.Columns("A:A").ColumnWidth = 5.71

This is my second week at Autoscript so I'm afraid I've never heard about "methods" before. So a did a google search on <Excel + VBA + method + sort> and came across the following article http://msdn.microsoft.com/en-us/library/aa213557%28office.11%29.aspx in which the following examples are given....

Sub SortRange1()

Worksheets("Sheet1").Range("A1:C20").Sort _

Key1:=Worksheets("Sheet1").Range("A1"), _

Key2:=Worksheets("Sheet1").Range("B1")

End Sub

Sub SortRange2()

Worksheets("Sheet1").Range("A1").Sort _

Key1:=Worksheets("Sheet1").Columns("A"), _

Header:=xlGuess

End Sub

I tried adapting this for my AutoIT script as follows. (I'm trying to sort the range A2:K78 by columns A, C and F

$oExcel.ActiveSheet.Range("A2:K78").Sort _

Key1:=ActiveSheet.Range("A"), _

Key2:=ActiveSheet.Range("C"), _

Key3:=ActiveSheet.Range("F")

Unfortunately AutoIT is objecting to the syntax.

I've also tried to put it on one line as follows but still it doesn't work.

$oExcel.ActiveSheet.Range("A2:K78").Sort Key1:=ActiveSheet.Range("A"),Key2:=ActiveSheet.Range("C"),Key3:=ActiveSheet.Range("F")

It seems as though AutoIT is objecting to the space between "Sort" and "Key1", which I believe is supposed to be there.

I also tried separating these two words with a period but it did not help.

I also tried a comma between the two words - to no avail.

Now I've just noticed that the title of the article is "Sort Method [Excel 2003 VBA Language Reference]" and I'm using Excel 2002 (Office XP).

Questions:

(1) Does Excel VBA 2002 support this method?

(2) If not, can I install Excel VBA 2003 if my version of Excel is only 2002 (Office XP)?

(3) What is the proper AutoIT syntax for the above?

(4) Where can I find a list of all methods available for scripting in Excel 2002

Once again, any suggestions would be greatly appreciated. Sample code would be even better.

[Juvigy]

Try something like :

$oExcel.ActiveSheet.Range("A1:D10").Sort($oExcel.ActiveSheet.Range("A1"),1,$oExcel.ActiveSheet.Range("B1"))

1 is ascending 2 is the opposite - or vise verse .

[CE101]

Hi Juvigy - thank you for responding.

I tried your sample code...

$oExcel.ActiveSheet.Range("A1:D10").Sort($oExcel.ActiveSheet.Range("A1"),1,$oExcel.ActiveSheet.Range("B1"))

However I get the following error message: "The requested action with this object has failed".

So I tried it with just one sortkey as follows and it works...

$oExcel.ActiveSheet.Range("A2:K600").Sort($oExcel.ActiveSheet.Range("A2"),1 )

(Note: I changed A1 to A2 because I don’t want to include the heading line in the sort)

Perhaps we are not using the right sytax for multiple sortkeys.

Perhaps we are using the wrong delimiter between sortkeys.

In your example the sortkeys are separated by a comma (ie the comma after the number 1)...

$oExcel.ActiveSheet.Range("A1"),1,

$oExcel.ActiveSheet.Range("B1")

So I replaced the comma with a semicolon, hoping this is the correct delimeter, however I got the following error: "Error Parsing Function Call"

; $oExcel.ActiveSheet.Range("A2:K600").Sort( $oExcel.ActiveSheet.Range("A2"),1 ; $oExcel.ActiveSheet.Range("C2"),1 ; $oExcel.ActiveSheet.Range("F2"),1 )

Then I tried enclosing each sortkey in parenthesis, separating them with commas. However I got the following error: "Missing right bracket in expression"

; $oExcel.ActiveSheet.Range("A2:K600").Sort( ($oExcel.ActiveSheet.Range("A2"),1) , ($oExcel.ActiveSheet.Range("C2"),1) , ($oExcel.ActiveSheet.Range("F2"),1) )

Then I tried enclosing each sortkey in parenthesis, separating them with periods.

$oExcel.ActiveSheet.Range("A2:K600").Sort($oExcel.ActiveSheet.Range("A2"),1).Sort($oExcel.ActiveSheet.Range("C2"),1).Sort($oExcel.ActiveSheet.Range("F2"),1 )

Although it sorted, it did not do so in the way I had hoped. It was sorted by col A, but not C and F. The seconday sequence remained col D (as it was before running this sort).

Besides finding the correct syntax, I'm wondering how to specify all cells except the heading line.

In my example above (A2:K600) I've placed some arbitrary bounds -- row 600 and column K.

However you never know - the file can be larger. So how do I make the sort flexible enough to cover everything?

Poking around a little more I discovered that Excel has a VBA macro recorder, so I recorded what happens when I do a couple of simple sorts.

In the first scenario, I started off by clicking the box at the top left corner of the spreadsheet to select all cells. Then I went to the "Data/Sort" menu and accepted the Sort defaults (sort on Column A, ascending, "My list has headings"). And the following code was generated...

Cells.Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

In the next scenario I selected three sortkeys and the following code was generated...

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _

, Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _

xlSortTextAsNumbers

As you can see there's much more to the sort than what we included in the $oExcel example above.

I'm curious how this stuff translates into our $oExcel format.

Once again, any suggestions would be greatly appreciated. Sample code would be even better.

[CE101]

In the meantime I've found a workaround.

Instead of one sort operation with three sortkeys, I'm doing three separate sorts.

$oExcel.ActiveSheet.Range("A2:Z2000").Sort($oExcel.ActiveSheet.Range("F2"),1 ) ; Date

$oExcel.ActiveSheet.Range("A2:Z2000").Sort($oExcel.ActiveSheet.Range("C2"),1 ) ; Contact

$oExcel.ActiveSheet.Range("A2:Z2000").Sort($oExcel.ActiveSheet.Range("A2"),1 ) ; Company

As a result the file will be sorted in the following order (by columns): A,C, F

In effect A is the major sortkey, followed by C and then F.

Note: I have set the range to A2:Z2000 to accommodate currents needs.

However you never know - the file could grow larger. So I would still like to know how to make the sort flexible enough to cover all cells except the heading line.

And I am still curious how this can be accomplished with Sortkeys.

[Juvigy]

This is for using all cells for the sort:

$oExcel.ActiveSheet.UsedRange.Sort($oExcel.ActiveSheet.Range("F2"),1 )

And this works for me with no errors / XP SP3 Office2007 :

#Include<array.au3>
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:\1.xls")
$oExcel.ActiveSheet.Range("A1:B10").Sort($oExcel.ActiveSheet.Range("A1"),1,$oExcel.ActiveSheet.Range("B1"))

[CE101]

Hi Juvigy - thanks for the code.

Regarding "UsedRange" ... I just discovered it yesterday... Very useful.

Regarding your second method

I still get the same error message: "The requested action with this object has failed".

Apparently this format will not work on my system - Win2000 SP3, Office XP/2002.

[CE101]

Hi Juvigy:

I just tried your first method...

$oExcel.ActiveSheet.UsedRange.Sort($oExcel.ActiveSheet.Range("A2"),1 )

Yes, it sorts everything... However it includes the heading line (row 1) in the sort.

I wonder if there's a way to exclude the heading line even though "UsedRange" has been specified.

[Juvigy]

No, but you can use something like UsedRange.Address to get the range dimensions and then you can select a new range without the first row.

[CE101]

Hi Juvigy

> use something like UsedRange.Address to get the range dimensions

Thank you for the suggestion.

Not being familiar with this one, I figured you mean something like this...

#include <Excel.au3>
$oExcel = _ExcelBookOpen("C:\Temp\temp1.xls") 
sleep (1500)
Dim $array[4]
$array = $oExcel.UsedRange.Address

Got the following message -- "The requested action with this object failed"

So I searched Forums and came across the following posting http://www.autoitscript.com/forum/index.php?showtopic=45676 in which Locodarwin says that the UsedRange property is buggy and for this reason he wrote his own UDF.

..... _ExcelSheetUsedRangeGet

Here's how I used it...

;-----------------------------------------------------------------
; This example shows how to use ExcelSheetUsedRangeGet 
; to do a sort on all rows and columns (except the heading line) 
; without knowing the dimensions (maximum rows and columns)  
;-----------------------------------------------------------------

; The following comment lines come from ExcelSheetUsedRangeGet.au3
;       Description:      Return the last cell of the used range in the specified worksheet.
;       Syntax:           $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet)
;       Return Value(s):  On Success - Returns an array of used range values:
;                            $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank)
;                            $array[1] - The last cell used, in R1C1 format
;                            $array[2] - The last column used, as an integer
;                            $array[3] - The last row used, as an integer

#include <\Non-Standard\ExcelSheetUsedRangeGet.au3>
#include <Excel.au3>

$oExcel = _ExcelBookOpen("C:\Temp\temp1.xls") 
sleep (1500)

Dim $array[4]
$array = _ExcelSheetUsedRangeGet($oExcel, 1)

; If the last cell is Z79, then $array will look like this... 
;     [0] "Z79" [1] "R79C26"    [2] 26     [3] 79   

$oExcel.ActiveSheet.Range("A2:" & $array[0]).Sort($oExcel.ActiveSheet.Range("A2"),1 )

[Juvigy]

This is how i use .address

#include <Array.au3>
        $oExcel = ObjCreate("Excel.Application")
        $oExcel.Visible = 1
        $oExcel.WorkBooks.Open ("C:\Test.xls")
        MsgBox(0,"",$oExcel.Activesheet.UsedRange.Address)

You should get something like "$A$1:$G$44"

[CE101]

Hi Juvigy:

Thank you for the example. Works fine.