Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: OLE sorting multiple columns excel
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:=xlTopToBottomMy 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.selectHow can I use the SORT function on my selection ?!?
xlAscending=1 xlGuess=0 False=0 xlTopToBottom= 1For 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)
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:=1How can I supply the values for Key1 and Key2 ?
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
File Created: 2002:09:05:13:50:52
Last Updated: 2002:09:05:13:50:52