Sort data with Excel via COM Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
jekko1976
Posts: 36
Joined: 10 Oct 2014, 07:03

Sort data with Excel via COM

23 Feb 2018, 05:43

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
Last edited by jekko1976 on 23 Feb 2018, 09:55, edited 1 time in total.
jekko1976
Posts: 36
Joined: 10 Oct 2014, 07:03

Re: Sorting data with Excel via COM

23 Feb 2018, 08:58

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
Posts: 1235
Joined: 30 Sep 2013, 22:25

Re: Sort data with Excel via COM  Topic is solved

23 Feb 2018, 13:51

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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
jekko1976
Posts: 36
Joined: 10 Oct 2014, 07:03

Re: Sort data with Excel via COM

23 Feb 2018, 14:26

Aaaaaand....IT WORKS!!!

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

Return to “Ask For Help”

Who is online

Users browsing this forum: Google [Bot], LLama and 101 guests