Sorting Multiple Columns

Keywords: 	 OLE sorting multiple columns excel

---

Question:

I want to sort an Excel sheet with OLE. So far, I can open the sheet, select the sort zone, but I am stuck there.

I did the sort using an Excel macro that looks like this:

Range("A1:C100").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

My code at this point is:

objExcel = ObjectOpen("Excel.Application")
objExcel.DisplayAlerts = @false
objExcel_Document = objExcel.WorkBooks
objExcel_Document.OpenText("test.xls")
objWork_Space = objExcel.activeworkbook
objWork_Sheet = objWork_Space.worksheets("test")
objWork_Sheet.Activate

objCell = objWork_Sheet.range("A1:C100")
objcell.select

How can I use the SORT function on my selection ?!?

Answer:

Make sure the following constants are defined in your code:

xlAscending=1
xlGuess=0
False=0
xlTopToBottom= 1

For information on constant values see Article ID: W14910.

This should work:

objExcel = ObjectOpen("Excel.Application")
objExcel.DisplayAlerts = @false

;objExcel.visible = @true
objExcel_Document = objExcel.WorkBooks
objExcel_Document.OpenText("C:\temp\test.xls")
objWork_Space = objExcel.activeworkbook
objWork_Sheet = objWork_Space.worksheets("test")
objWork_Sheet.Activate
objRange = objWork_Sheet.Range("A1:C100")

;objRange.select
xlAscending = 1
xlGuess = 0
xlSortColumns = 1 
objRange.Sort(:: Key1=objRange,Order1=%xlAscending%, Header=%xlGuess%, OrderCustom=1, MatchCase=0, Orientation=%xlSortColumns%)

ObjectClose(objExcel_Document)
ObjectClose(objWork_Space)
ObjectClose(objExcel)

Question (cont'd):

YES!!!!!!!

This works. Now the last step: sort on multiple columns.

If I select multiple columns with:

objRange = objWork_Sheet.Range("A:Z")

The macro gives:

Selection.Sort Key1:=Range("B1"), Order1:=1, Key2:=Range("A1"), Order2:=1, Header:=0,
OrderCustom:=1, MatchCase:=0, Orientation:=1

How can I supply the values for Key1 and Key2 ?

Answer:

This will do it:

objRange = objWork_Sheet.Range("A:BZ")
key_1 = objWork_Sheet.Range("A2")
key_2 = objWork_Sheet.Range("H2")
objRange.Sort(:: Key1=key_1, Order1=1, Key2=key_2, Order2=1, Header=1, OrderCustom=1,
MatchCase=0, Orientation=1)

---

Article ID:   W15259