Ask Your Question
1

Macro Sort only allows 3 criteria

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

EquipLordBritish gravatar image

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

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 +0100 )edit

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

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

Updated the post with the macro.

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

2 Answers

Sort by » oldest newest most voted
1

answered 2020-06-18 01:49:53 +0100

ejubin gravatar image

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

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

librebel gravatar image

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

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 +0100 )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 +0100 )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 +0100 )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 +0100 )edit

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

librebel gravatar imagelibrebel ( 2017-07-27 05:11:21 +0100 )edit

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)

ejubin gravatar imageejubin ( 2020-06-18 01:11:21 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 1,257 times

Last updated: Jun 18 '20