Support Portal

for ProcessRobot and WinAutomation

Start a new topic
Answered

Excel columns sorting

Hi, let’s say I have a simple Excel file with 2 columns and first string headers. What is the best way to sort file by column A then by column B using WinAutomation 8?

Best Answer

Hi Roman!

In general, when automating such processes within Excel, it is recommended to use the built-in capabilities of Excel itself.

Keeping this in mind, a simple solution would be to select the range of the data which you wish to sort (by using the "Select Cells in Excel Worksheet" action), and use the Excel interface to perform the sorting (Data > Sort, then add the levels you wish and configure accordingly). Navigating the Excel ribbon and popup windows can be performed as in any other page, by using the appropriate selectors, or Excel's keyboard shortcuts.


ADMIN
Answer

Hi Roman!

In general, when automating such processes within Excel, it is recommended to use the built-in capabilities of Excel itself.

Keeping this in mind, a simple solution would be to select the range of the data which you wish to sort (by using the "Select Cells in Excel Worksheet" action), and use the Excel interface to perform the sorting (Data > Sort, then add the levels you wish and configure accordingly). Navigating the Excel ribbon and popup windows can be performed as in any other page, by using the appropriate selectors, or Excel's keyboard shortcuts.


1 person likes this
Hi Romanos, Thanks for your answer! I thought about going that way but hoped there’s a more efficient approach.

Hi Roman, you can also use a vbscript action to do what your looking for, copy the below to a vbscript action and replace the filename variable with your file:

 

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%Filename%")
objExcel.Visible = True ' can set to False to not see it
Set objWorksheet = objWorkbook.Sheets("Sheet1")
Set objRange = objWorksheet.UsedRange

set sortCol1 = objWorksheet.Range("A1")
set sortCol2 = objWorksheet.Range("B1")


'XlSortOrder Enum
'VBA uses xlAscending and the like, we canot use that in VBS so use the 0,2,1.
'xlAscending	1	default. Sorts the specified field in ascending order.
'xlDescending	2	Sorts the specified field in descending order.
Const xlAscending = 1
Const xlDescending = 2

'==============================================================================
'XlYesNoGuess Enum
'VBA uses xlGuess and the like, we canot use that in VBS so use the 0,2,1.
'xlGuess	0	Excel determines whether there’s a header, and to determine where it is, if there is one.
'xlNo		2	default. (The entire range should be sorted).
'xlYes		1	(The entire range should not be sorted).
Const xlGuess = 0
Const xlYes = 1
Const xlNo = 2

'expression. Sort (_Key1_, _Order1_, _Key2_, _Type_, _Order2_, _Key3_, _Order3_, _Header_, _OrderCustom_, _MatchCase_, _Orientation_, _SortMethod_, _DataOption1_, _DataOption2_, _DataOption3_)

objRange.Sort sortCol1, xlAscending, sortCol2, ,xlAscending, , , xlYes

objWorkbook.Save
objWorkbook.close
objExcel.quit

 


2 people like this
ADMIN

Thank you very much Joseph!

 

We also created a relative article 

https://support.softomotive.com/solution/articles/35000095480-excel-sorting

Login or Signup to post a comment