CODEWITHSUNDEEP
macroslibreofficelibreoffice-calc
iND
iND

Reputation: 2649

LlibreOffice macro fails to sort the filtered results

This is a LibreOffice Calc macro. The goal is to filter unique data from a column on one worksheet, paste it on another worksheet, and then sort the pasted values. However, the output column is not getting sorted.

With sheets "final" and "lists", data is being read from $final.$m2:$m100 and is ending up in column $lists.$C, starting at $lists.$C2. The filtering works correctly, grabbing only the unique rows including empty cells (which is desired).

There are three subs/functions:

Do you see anything wrong with the code?

sub runit

    filterDistinct( "final", "$m2:$m100", "lists", "$C2", false, false)

end sub

Sub filterDistinct( _
    strSourceSheet As String, _
    strSourceRange As String, _
    strTargetSheet As String, _
    strTargetCell As String, _
    Optional bContainsHeader As Boolean, _
    Optional bCaseSensitive As Boolean _
)
    GlobalScope.BasicLibraries.loadLibrary("Tools")

    ' Uses a Filter to copy distinct rows from the 
    '   specified Source Range into a new Range that starts from the specified Target Cell.
    '
    ' <strSourceRange>    : specifies the Range to find distinct rows in, e.g. "A1:B99".
    ' <strTargetCell>     : specifies the Cell to put the first found distinct row in, e.g. "D1".
    ' <bContainsHeader>   : OPTIONAL - pass TRUE if the Source Range contains a Header.
    ' <bCaseSensitive>    : OPTIONAL - pass TRUE if case matters while searching for distinct rows.

    Dim oSheet As Object, oTargetSheet As Object, oSourceRange As Object, oTargetRange As Object, oFilter As Object

    oSheet = ThisComponent.getSheets().getByName(strSourceSheet)
    oSourceRange = oSheet.getCellRangebyName( strSourceRange )
    oTargetSheet = ThisComponent.getSheets().getByName(strTargetSheet)
    oTargetRange = oTargetSheet.getCellRangebyName( strTargetCell )
    oTargetSortRange = oTargetSheet.getCellRangebyName( "$C1:$C40000" )

    'msgbox oSheet.getName()
    'msgbox oSourceRange.AbsoluteName
    'msgbox oTargetSheet.getName()
    'msgbox oTargetRange.AbsoluteName
    'msgbox oTargetSortRange.AbsoluteName
    'sortRange( oTargetSortRange, 0, 1 )
    'exit sub

    oFilter = oSourceRange.createFilterDescriptor( True )
    oFilter.SkipDuplicates = True
    oFilter.CopyOutputData = True
    oFilter.OutputPosition = oTargetRange.CellAddress

    If Not IsMissing( bContainsHeader ) Then oFilter.ContainsHeader = bContainsHeader
    If Not IsMissing( bCaseSensitive ) Then oFilter.IsCaseSensitive = bCaseSensitive

    oSourceRange.filter( oFilter )

    sortRange( oTargetSortRange, 1, 1 ) 
End Sub


' sorts the cell range xRange by the iColumn (0 = first) column in IsAscending (=T/F) order: 
function sortRange( _
    xRange As Object, _
    iColumn As Integer, _
    IsAscending As Boolean _
)
    GlobalScope.BasicLibraries.loadLibrary("Tools")

    Dim oSortDesc(4) As New com.sun.star.beans.PropertyValue
    Dim aSortFields As Object
    ' Dim oField As New com.sun.star.sheet.TableFilterField2

    'oField.Field=0
    'oField.Operator=com.sun.star.sheet.FilterOperator2.NOT_EMPTY
    'oFilterDesc.setFilterFields2(Array(oField))

    ' define the fields to sort
    aSortFields = New com.sun.star.util.SortField
    aSortFields.Field = iColumn
    aSortFields.SortAscending = IsAscending

    ' define the sort descriptor
    oSortDesc(0) = new com.sun.star.beans.PropertyValue
    oSortDesc(0).Name = "SortFields"
    oSortDesc(0).Value = aSortFields

    oSortDesc(1) = new com.sun.star.beans.PropertyValue
    oSortDesc(1).Name = "ContainsHeader"
    oSortDesc(1).Value = true

    oSortDesc(2) = new com.sun.star.beans.PropertyValue
    oSortDesc(2).Name = "IsCaseSensitive"
    oSortDesc(2).Value = False

    oSortDesc(3) = new com.sun.star.beans.PropertyValue
    oSortDesc(3).Name = "ContainsHeader"
    oSortDesc(3).Value = True

    ' Select the range and sort it.
    ' ThisComponent.getCurrentController.select( xRange )

    msgbox xRange.AbsoluteName
    xRange.Sort( oSortDesc )    

    'dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

end function

Upvotes: 0

Views: 674

Answers (1)

Jim K
Jim K

Reputation: 13790

There are several small problems.

  • The range only has one column, so iColumn must be 0 to use the first and only column.
  • aSortFields should be an array.
  • sortRange it is used as a subroutine, not a function. It may still work, but it's not right.

Here is example working code based on Listing 6.28: Descending sort in a Calc document from Andrew's macro document.

Sub doSort
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oTargetSortRange = oSheet.getCellRangebyName( "$C1:$C40000" )
    sortRange( oTargetSortRange, 0, 1 ) 
End Sub

' sorts the cell range xRange by the iColumn (0 = first) column in IsAscending (=T/F) order: 
Sub sortRange( _
    xRange As Object, _
    iColumn As Integer, _
    IsAscending As Boolean _
)
    Dim oSortDesc(0) As New com.sun.star.beans.PropertyValue
    Dim aSortFields(0) As New com.sun.star.util.SortField

    aSortFields(0).Field = iColumn
    aSortFields(0).SortAscending = IsAscending

    oSortDesc(0).Name = "SortFields"
    oSortDesc(0).Value = aSortFields()

    xRange.Sort( oSortDesc )    
End Sub

Upvotes: 1

Related Questions