topic RE: Sort Excel column by custom order in Product Forum

Sort Excel column by custom order

RohiniPatil — Wed, 29 Sep 2021 14:38:00 GMT

Hi,

I want to sort column from excel file by predefined order. how can we achieve it?

------------------------------
Rohini Patil
------------------------------

RE: Sort Excel column by custom order

VedSengupta — Thu, 30 Sep 2021 10:58:00 GMT

Hello Rohini,
You can run excel Macors ( run Macros action is available) to sort columns.

------------------------------
------------------------------
Best Regards,
Ved Sengupta
RPA Developer
Deloitte India (Offices of the US)
Bangalore | INDIA
*If you find this post helpful mark it as best answer*
------------------------------
------------------------------

RE: Sort Excel column by custom order

RohiniPatil — Thu, 30 Sep 2021 12:52:00 GMT

for that 1st i have to create macro in the file right? we are downloading this file for application then how can i add macro to it?

RE: Sort Excel column by custom order

EVIPUTI — Sun, 03 Oct 2021 11:26:00 GMT

Try using the below code :

Dim worksheet,Excel As Object

Dim rRng as Object

Dim Header1 as integer

try

Excel = GetWorkbook(handle,Nothing)

worksheet = GetWorkbook(handle,Nothing).ActiveSheet

rRng = worksheet.Range(Data_StartCell,Data_EndCell)

if HeaderPresent = true then

    Header1=1

else if HeaderPresent = false then

    Header1=2

end if

rRng.Sort(Key1:=worksheet.Range(Order_StartCell,Order_EndCell), Order1:=OrderType,Header:=Header1)

Success = true

catch e as Exception

Success = false

End try

Input Variables Used- handle , Data_StartCell , Data_EndCell, Order_StartCell, Order_EndCell, OrderType, HeaderPresent

Output Variables - Success

Suppose you want have data from A1:E20 column and you need to sort column B only. So below are the inputs which you can use:

Data_StartCell- "A1"

Data_EndCell - "E20"

Order_StartCell- "B1"

Order_EndCell- "B20"

OrderType- 1 (it is of Number type and 1 for Ascending and 2 for Descending)

HeaderPresent- True or False depending you have headers or not

Hope the solution works for you.

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification Developer
Amazon
------------------------------
------------------------------

RE: Sort Excel column by custom order

EVIPUTI — Sun, 03 Oct 2021 11:26:00 GMT

Also do check this thread also .

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification Developer
Amazon
------------------------------
------------------------------