Sort data with Excel via COM Topic is solved

[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

[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

[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

[jekko1976]

Aaaaaand....IT WORKS!!!

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