Page 1 of 1

Sort data with Excel via COM

Posted: 23 Feb 2018, 05:43
by jekko1976
Hello,

i need to sort data in an Excel sheet.
The default VBA code for sorting is this

Code: Select all

Range(“A1”).CurrentRegion.Sort _
Key1:=Range(“E1”), _
Order1:=XLAscending, _
Header:=XLYes
I tried to "translate" it in the COM method like this:

Code: Select all

oExcel:=ComObjActive("Excel.Application")
oExcel.ActiveSheet.Range(“A1”).CurrentRegion.Sort Key1:=Range(“E1”), Order1:=XLAscending, Header:=XLYes
But the compiler return me this error
Call to nonexistent function.
Specifically: Range(“E1”), Order1:=XLAscending, Header:=XLYes

Could someone help me with the correct syntax?

Thank you very much

Re: Sorting data with Excel via COM

Posted: 23 Feb 2018, 08:58
by jekko1976
Update:

I found this post https://autohotkey.com/boards/viewtopic ... rt#p199859

where FanaticGuru give this solution

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.ActiveSheet.Range("A1:B10").Sort(xlApp.Range("A1"))
The commands actually works but i have left two problems to resolve:

1) I am not able to select the complied data area of my worksheet (CurrentRegion)
2) Excel doesn't understand that the very fist line (line 1) is the title line so tries to sort it aswell

Could someone help me?
thank you very much

Re: Sort data with Excel via COM  Topic is solved

Posted: 23 Feb 2018, 13:51
by FanaticGuru
This will sort with a header.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.Range("A1:B10").Sort(xlApp.Range("A1"),,,,,,,1) ; with Header
This will sort the current region based on what cell is active. Assumes header and sorts by first column.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlRange_Sort := xlApp.ActiveCell.CurrentRegion
xlRange_SortKey := xlRange_Sort.Columns(1)
xlRange_Sort.Sort(xlRange_SortKey,,,,,,,1)
This is some constants and parameter order that might help.

Code: Select all

;  Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

; Header: xlYes := 1, xlNo := 2, xlGuess := 0
; Order: xlAscending := 1, xlDescending := 2
FG

Re: Sort data with Excel via COM

Posted: 23 Feb 2018, 14:26
by jekko1976
Aaaaaand....IT WORKS!!!

Thank you very much FanaticGuru, you solved me a big big problem!