Macro Sort only allows 3 criteria

English
, ,
#1

Running a sort in libreoffice 5.3.4.2 (x64) on windows 10 allows you to make a large number of sort criteria. If you attempt to record a macro of a sort with more than 3 criteria, the macro only records the first 3 and ignores the rest, although the initial sort that is done works fine.

Attempting to manually add more sort criteria into the macro results in a macro that still only sorts the first 3 criteria in the list, although it does technically have more sort criteria in it.

Is this a bug or is there some other way to sort more than 3 criteria with a macro?

Recorded macro from a 5 column sort:

sub SortColumnB
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$5:$F$14"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(12) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ByRows"
args2(0).Value = true
args2(1).Name = "HasHeader"
args2(1).Value = false
args2(2).Name = "CaseSensitive"
args2(2).Value = false
args2(3).Name = "NaturalSort"
args2(3).Value = false
args2(4).Name = "IncludeAttribs"
args2(4).Value = true
args2(5).Name = "UserDefIndex"
args2(5).Value = 0
args2(6).Name = "Col1"
args2(6).Value = 2
args2(7).Name = "Ascending1"
args2(7).Value = true
args2(8).Name = "Col2"
args2(8).Value = 3
args2(9).Name = "Ascending2"
args2(9).Value = true
args2(10).Name = "Col3"
args2(10).Value = 4
args2(11).Name = "Ascending3"
args2(11).Value = true
args2(12).Name = "IncludeComments"
args2(12).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())


end sub

Manually modified macro (that still only sorts 3 columns):

sub SortColumnC
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$5:$F$14"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(16) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ByRows"
args2(0).Value = true
args2(1).Name = "HasHeader"
args2(1).Value = false
args2(2).Name = "CaseSensitive"
args2(2).Value = false
args2(3).Name = "NaturalSort"
args2(3).Value = false
args2(4).Name = "IncludeAttribs"
args2(4).Value = true
args2(5).Name = "UserDefIndex"
args2(5).Value = 0
args2(6).Name = "Col1"
args2(6).Value = 2
args2(7).Name = "Ascending1"
args2(7).Value = true
args2(8).Name = "Col2"
args2(8).Value = 3
args2(9).Name = "Ascending2"
args2(9).Value = true
args2(10).Name = "Col3"
args2(10).Value = 4
args2(11).Name = "Ascending4"
args2(11).Value = true
args2(12).Name = "Col4"
args2(12).Value = 5
args2(13).Name = "Ascending5"
args2(13).Value = true
args2(14).Name = "Col5"
args2(14).Value = 6
args2(15).Name = "Ascending6"
args2(15).Value = true
args2(16).Name = "IncludeComments"
args2(16).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())


end sub
#2

Didn’t you forget to include something? Say, the macro?

#3

Hello @EquipLordBritish,
Please post your recorded macro here so we can inspect the details.

#4

Updated the post with the macro.

#5

Hello @EquipLordBritish,

The dispatch recorder is limited in this respect.
It is possible to sort by more than 3 fields, but only the first 3 sort fields have a named property predefined for them which the dispatch recorder can supply.
In order to sort by more than 3 fields, you’d need to pass an array of sort fields to the sortDescriptor’s “SortFields” property.

I wrote the following macro based on your method SortColumnC(), but without the dispatcher, please try if it works for you:

Sub SortColumnC()
    REM
	Const sCellRangeName As String = "$B$5:$F$14"			REM put here your cell range to sort.

	Dim oSheet As Object, oCellRange As Object
	oSheet = ThisComponent.CurrentController.ActiveSheet
	oCellRange = oSheet.getCellRangeByName( sCellRangeName )
	
    REM put here your sort columns:
    REM Field 0 is the first column in the range.
	Dim oSortFields(4) As New com.sun.star.util.SortField	REM SortField works but is deprecated... -> TableSortField 
	oSortFields(0).Field = 0				REM sort by the first column of the range ( = Column B ),
	oSortFields(0).FieldType = com.sun.star.util.SortFieldType.NUMERIC
	oSortFields(0).SortAscending = True
	oSortFields(1).Field = 1				REM  then by the 2nd column of the range,
	oSortFields(1).FieldType = com.sun.star.util.SortFieldType.NUMERIC
	oSortFields(1).SortAscending = True
	oSortFields(2).Field = 2				REM   then 3rd,
	oSortFields(2).FieldType = com.sun.star.util.SortFieldType.NUMERIC
	oSortFields(2).SortAscending = True
	oSortFields(3).Field = 3				REM    etc. 4th,
	oSortFields(3).FieldType = com.sun.star.util.SortFieldType.NUMERIC
	oSortFields(3).SortAscending = True
	oSortFields(4).Field = 4				REM     5th.
	oSortFields(4).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC
	oSortFields(4).SortAscending = True
	
	Dim oSortDesc(9) As New com.sun.star.beans.PropertyValue
	REM TableSortDescriptor2 Attributes:
	oSortDesc(0).Name = "SortFields"
	oSortDesc(0).Value = oSortFields()
	oSortDesc(1).Name = "MaxSortFieldsCount"	REM the maximum number of sort fields that this descriptor can hold.
	oSortDesc(1).Value = 1 + uBound( oSortFields )
	oSortDesc(2).Name = "IsSortColumns"			REM False=Sort by Row; True=Sort by Column.
	oSortDesc(2).Value = False
	
	REM TableSortDescriptor Attributes:
	oSortDesc(3).Name = "ContainsHeader"
	oSortDesc(3).Value = False
	
	REM SortDescriptor Attributes:
	oSortDesc(4).Name = "IsCaseSensitive"
	oSortDesc(4).Value = False

	REM SheetSortDescriptor Attributes:
	oSortDesc(5).Name = "BindFormatsToContent"
	oSortDesc(5).Value = True
'	oSortDesc(6).Name = "CopyOutputData"		REM specifies if the sorted data should be copied to another position. 
'	oSortDesc(6).Value = False
'	oSortDesc(7).Name = "OutputPosition"
'	oSortDesc(7).Value = <oCellAddress>
	oSortDesc(8).Name = "IsUserListEnabled"		REM specifies if a user-defined sorting list is used. 
	oSortDesc(8).Value = True
'	oSortDesc(9).Name = "UserListIndex"
'	oSortDesc(9).Value = 0
	
	REM Select the range and sort it.
	ThisComponent.getCurrentController.select( oCellRange )
	oCellRange.Sort( oSortDesc )
End Sub
#6

This still does not appear to sort beyond 3 columns.

I made 3 columns that are all '1’s and scrambled the next two from 0-9. I copy-pasted the macro and ran it. The scrambled columns were not sorted.

If I scramble any of the first 3 columns with different numbers, it will sort those, but not beyond 3.

Edit: Have you run it successfully?

#7

You’re right @EquipLordBritish, it indeed does not appear to sort beyond 3 columns. Sorry i had not tested this before. Also for sorting by Column, it does not seem to sort beyond 3 rows.

I think this deserves a feature enhancement request.

#8

Hello @EquipLordBritish,

Good news, i got the macro to work after all.

The only thing missing was the field type for each of the 5 sort fields…

i edited my answer to include the updated macro.

#9

What version are you running? This still does not seem to sort the last columns on mine. (5.3.4.2 (x64) Windows 10)

Calc - how to make calc remember that first row is title row when sorting
#10

running Version: 5.3.1.2 Build ID: 1:5.3.1-0ubuntu2

#11

Did this work for anyone else? I’ve got the same issue trying to sort rows based on 11 different columns. The above macro only sorts based on the first three. Version: 6.3.6.2 (x64)

#12

I found this solution that worked for any number of columns:
https://forum-test.openoffice.org/en/forum/viewtopic.php?f=20&t=94362

Disclaimer: not my code, but tested and it worked on LO v6.

Sub SortCols()
   Dim sSheetName as String, sRange as String
   Dim Order as Variant, isAscendent as Variant
   Dim oSheet, oCellRange, i as Integer
   Dim aSortFields(0) as New com.sun.star.table.TableSortField, SortDesc
   sSheetName = "Sheet1" 'set the sheet name
   sRange = "G1:M17" 'set the range to sort
   'For setting the order to sort, first column is 1,
   'so if the order is G, J, M, K the array should be 1,4,7,5
   'If there's a column in the range you don't use to sort, ignore it
   'For exemple: sRange = "C1:J17" and Order = Array(2,4,5,3)
   Order = Array(1,3,4,2) 'set the order for sorting: G(1), J(4), M(7), K(5)
   isAscendent = Array(True,True,True,True) 'set True=1 or False=0 for each column to sort
   
   oSheet = ThisComponent.Sheets.getByName(sSheetName)
   oCellRange = oSheet.getCellRangeByName(sRange)
   SortDesc = oCellRange.CreateSortDescriptor
   SortDesc(1).Value = True 'ContainsHeader
   
   For i = Ubound(Order) to 0 Step -1
      aSortFields(0).Field = Order(i)-1   'index = 0 is first column
      aSortFields(0).IsAscending = isAscendent(i)
      SortDesc(3).Value = aSortFields()
      oCellrange.Sort(SortDesc)
   Next
End Sub

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.