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] [Download] GeSHi © Codebox Plus

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] [Download] GeSHi © Codebox Plus

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.php?f=5&t=43412&p=199859&hilit=excel+sort#p199859

where FanaticGuru give this solution

Code: [Select all] [Download] GeSHi © Codebox Plus

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: 1166
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] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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] [Download] (Untitled.ahk)GeSHi © Codebox Plus

;  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

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[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: chngrcn, pro100andrik94, Zumwalt and 84 guests