Ask Your Question
1

Macro Sort only allows 3 criteria

asked 2017-07-18 22:21:14 +0200

EquipLordBritish gravatar image

updated 2017-07-18 22:38:34 +0200

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 ...
(more)
edit retag flag offensive close merge delete

Comments

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-07-18 22:31:19 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-07-18 22:35:28 +0200 )edit

Updated the post with the macro.

EquipLordBritish gravatar imageEquipLordBritish ( 2017-07-18 22:38:59 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2017-07-19 03:31:31 +0200

librebel gravatar image

updated 2017-07-21 09:51:37 +0200

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
edit flag offensive delete link more

Comments

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?

EquipLordBritish gravatar imageEquipLordBritish ( 2017-07-19 20:22:45 +0200 )edit

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.

librebel gravatar imagelibrebel ( 2017-07-20 11:10:22 +0200 )edit

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.

librebel gravatar imagelibrebel ( 2017-07-21 09:52:26 +0200 )edit

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

EquipLordBritish gravatar imageEquipLordBritish ( 2017-07-26 19:32:22 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-07-27 05:11:21 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-18 22:21:14 +0200

Seen: 526 times

Last updated: Jul 21 '17