I’m trying to develop a macro that allows me to define and save AutoFilter settings so that I can quickly impose complex filters on a sheet. The sheet holds a database with 17 columns, Headers in the first row, and AutoFilter in place. Here is some prototype code that imposes a filter showing rows where column A EQUALS “Value1” and column K CONTAINS “Value2”:
Sub CreateFilter
Dim theRange As Object ' A Range Object
Dim theFilterDesc As Object ' A FilterDescriptor Object
Dim theFilterFields() As Object ' An Array of FilterField objects
Dim aFilterField As Object ' A single item for the FilterFields Array
' Get the Database Range we're going to filter
theRange = ThisComponent.getPropertyValue("DatabaseRanges").getByName("ImportDatabase")
ReDim theFilterFields(1) ' Redimension the FilterFields array
aFilterField = New com.sun.star.sheet.TableFilterField ' Create a FilterField
aFilterField.Connection = 0 ' 0 = AND, 1 = OR (irrelevant for item 0)
aFilterField.IsNumeric = FALSE ' Define an EQUAL filter
aFilterField.StringValue = "Value1"
aFilterField.Field = 0
aFilterField.Operator = com.sun.star.sheet.FilterOperator.EQUAL
theFilterFields(0) = aFilterField ' Add the first FilterField to the FilterFields array
aFilterField.Connection = 0 ' Define a CONTAINS filter
aFilterField.IsNumeric = FALSE
aFilterField.StringValue = "Value2"
aFilterField.Field = 10
aFilterField.Operator = com.sun.star.sheet.FilterOperator2.CONTAINS
theFilterFields(1) = aFilterField ' Add the second FilterField to FilterFields array
theFilterDesc = theRange.getFilterDescriptor(TRUE) ' Get an EMPTY FilterDescriptor
With theFilterDesc ' Set
.ContainsHeader = TRUE ' the
.CopyOutputData = FALSE ' Properties
.IsCaseSensitive = FALSE ' of the
.UseRegularExpressions = FALSE ' FilterDescriptor
End With ' itself
theFilterDesc.setFilterFields(theFilterFields) ' Place the FilterFields array into the descriptor
theRange.refresh() ' Finally, invoke the filter
End Sub
That code works fine when trying to filter Column A to a SINGLE value. The issue I’m struggling with is trying to create a filter where, for example, Column A can equal any of MULTIPLE values.
If I use the Calc user interface to manually create and apply such a filter, I simply click the Column-A AutoFilter pull-down, and check the boxes to select the multiple values of interest.
Examining the FilterDescriptor that Calc creates for that filter, I see that the multiple values are declared within theRange.FilterDescriptor.FilterFields3(0).Values(n), where each Values(n) declares the properties for a single value selected with a checked box.
I simply cannot figure out how to properly declare or populate a TableFilterField for such a filter or how to add a TableFilterField3 with multiple Values to theFilterFields array.
Additional Info added following my initial post:
I can create a TableFilterField3 and set its properties using:
Dim aFilterField3 As Object
aFilterField3 = New com.sun.star.sheet.TableFilterField3
aFilterField3.Connection = 0
aFilterField3.Field = 10
aFilterField3.Operator = com.sun.star.sheet.FilterOperator2.EQUAL
And I could conceivably add it using theFilterDesc.setFilterFields3(theFilterFields). But aFilterField3 gets created with an empty array for its aFilterField3.Values property and I cannot figure out how to re-dimension that property so I can populate it with values. And, even if I could dimension the Values property array to allow me to assign a separate Values entry for each of the multiple values I’m trying to filter for, each of those Values is itself a structure with properties of its own. And I cannot figure out how to declare such an object so I can specify Values.IsNumeric and Values.StringValue.
Anyone have any ideas?
on it…
Hopefully what I’ve just told you will help you find your way to a solution.