Macro to create multi-value Calc FilterDescriptor

Chucko1 · August 5, 2025, 12:54pm

I’m trying to develop a Basic macro that can create an XSheetFilterDescriptor to filter the rows of a Calc sheet to display rows for which the string values in Column A match one of several string values in the same way as an AutoFilter does when only a few individual values are selected. However, no matter what I do, the result seems to ignore all but the first specified value. Here is my code:

    Dim oSheet As Object
    Dim oFilter As Object
    Dim filterValues() As Variant
    Dim filterFields(0) As new com.sun.star.sheet.TableFilterField3
    Dim i As Integer
    Dim op as Long

    ' Get the active sheet
    oSheet = ThisComponent.getCurrentController.getActiveSheet()
    ' Define the filter values to be matched
    filterValues = Array("Apple", "Banana", "Cherry")    
    Dim vals(UBound(filterValues)) as new com.sun.star.sheet.FilterFieldValue
    ' Create a new empty filter descriptor
    oFilter = oSheet.CreateFilterDescriptor(True)
    ' Populate filter conditions
    For i = 0 To UBound(filterValues)
        filterFields(0).Connection = com.sun.star.sheet.FilterConnection.OR
        filterFields(0).Field = 0  ' Column A
        filterFields(0).Operator = com.sun.star.sheet.FilterOperator2.EQUAL
        Dim val As New com.sun.star.sheet.FilterFieldValue
        val.IsNumeric = False
        val.StringValue = filterValues(i)
        val.FilterType = com.sun.star.sheet.FilterFieldType.STRING
        vals(i) = val
    Next i
    filterFields(0).Values = vals    
    ' Apply the filter
    oFilter.setFilterFields3(filterFields)
    oSheet.filter(oFilter)

When run, it displays only rows holding “Apple” in column A, the other rows are hidden, even if they contain one of the other specified values.

Any assistance would be appreciated.

sokol92 · August 5, 2025, 1:42pm

Have you tried this approach?

Chucko1 · August 5, 2025, 5:38pm

Thanks for the suggestion @sokol92. I’m both surprised and honored to see some of my own code posted back to me as part of a proposed solution. It turns out that I’m the @wrighch named in the embedded comment that links to a 2023 post to openoffice.org. Somehow, I lost the @wrighch handle when the site transitioned to ask.libreoffice several years ago.

After finding and posting that solution in 2023, I stopped updating my LibreOffice release to prevent changes in the UNO object model from breaking my code. But, after several years of running LibreOffice v7.2, I decided I should upgrade. So I’m trying to make my code run under V25, which, apparently, uses the LibreOffice 24.2 SDK APIs and which has again broken my code.

The example you suggested sets a filter that selects a SINGLE value for the named column using the TableFilterField3.Values property. And it works as advertised. I’m trying to create a TableFilterField3.Values array with multiple items, so the filter will show rows that match any value in the Values array. But in all the testing I’ve done so far, the 24.2 API oFilterDesc.setFilterFields3 method apparently honors only the first value in the array whereas the v7.2 APIs honored all the values.

Still struggling with this.

sokol92 · August 5, 2025, 5:43pm

Tomorrow we will redouble our efforts.