Hi everybody… and sorry for my bad english.
I’m trying to understand, if it is possible in Calc, to assigning a filter to an object/button.
I have a Spreadsheet, where i have some columns with names, containing data in rows.
I don’t be able to find a way, to filter for example a determinated data of the column “G” , and then
assigning this filter to an object/button, and calling THIS filtration by “pressing” the assigned button.
Is there any way to obtain this result already using macros maybe?
I hope that anyone understands my request and can help me.
Thank You!
This iis very easy to do with a database where you define queries that return any subset of columns and rows in any order of columns and rows.
Calc macros performing filter actions are rather difficult. See Macro in Basic - how to turn on Regular expression in Macro that uses an advance filter in one named range to filter another named range - #2 by Villeroy
1 Like
If you upload an example file and describe the required autofilter, then probably someone will write the necessary macro.
Alright, I did it again.
Attached spreadsheet has a documentation sheet, a source table in datbase range “Data” and a filtered output table in database range “Import1”.
All the filters and sort orders are defined in the attached database document “QBL” (Query By Listbox). The database is connected to the spreadsheet. It treats the spreadsheet as if it were a database.
How to make the sample work:
Download both files, call Tools>Options>Base>Databases and register the downloaded database file as “QBL”. Open the spreadsheet, choose the “Filter” sheet and use the listbox which contains query names.
This would be much better with a true database. Database data stored in spreadsheets are a mess.
QBL.odb (3.8 KB)
query_by_listbox.ods (81.2 KB)
1 Like
GesMag_Example1.ods (378.2 KB)
Hi sokol92, hi everybody. Thanks for support.
The attached example, is very similar to my real Spreadsheet… only the Graphic Look, and very many other records are absent. I’m sure that it don’t needs presentations for the work it does.
It contains macros, for locking the pages, and page change… and functions/conditions too.
The work is in italian, but I translated the interested fields, and think the best way to obtain a working result is to not hide anything. If you lock the page the unlock psw is 123456.
This protection is just to protect the sheet against accidentally changes… nothing else.
Ok let’s go…
Like you can see, the “Magazzino” page has named columns, and data is inserted in rows.
So it would be fantastic, if I can filter for example the “Category” column, to obtain and show only the
rows containing the “PMMA” data… and assign this “filtration event” to the shape in the header (or to any other “thing” you can suggest me.
Then, it would be very super, if i can filter another column, the “In Stock” for example, to show me only the RED values (or the Yellow, or only 0 values) of the “PMMA”… and assing this “filtration event” to another shape in the header.
And so on… in addictive mode, I’m tempted to say.
The intent of all this, is to make it easy to anyone to filter this principal records in easy way.
Would be for me, I can use the automatic filter… but I’m not the only user, unfortunately…
If anyone can show me, how to implement this “macros?” in general way, after I will adapt them
to my special needs(or I try), whenever possible for you.
Thanks for all who can help me.
I agree. Nevertheless, let’s try.
@GioC79 , please, read the description of the AddFilterField function, which sets (removes) a filter on an autofilter field and try the TestAddFilterField macro with various parameters.
Option Explicit
Sub TestAddFilterField()
Dim oSheet
oSheet=ThisComponent.Sheets.getByName("Magazzino")
AddFilterField oSheet, "Category", 2, "PMMA" ' 2: EQUAL
AddFilterField oSheet, "L. mm", 2, Array(2030, 3050) ' 2: EQUAL
' Also try this:
' AddFilterField oSheet, "Category", 2, Array("ACCIAIO-AISI304", "ALLUMINIO-FRE")
' AddFilterField oSheet, "L. mm", -1 ' Remove filter from "L. mm" field
End Sub
' -------------------------------------------------------------------------------------------------
' Adds a field to an existing autofilter.
' Params:
' obj DataBaseRange object or spreadsheet.
' field field number in the autofilter range or field name.
' operator is specified according to the com.sun.star.sheet.FilterOperator enumeration:
' https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet.html#af9e5fd8fd26fc252748d97ebd68ea6b1
' To remove a field from the filter, specify -1.
' value value (scalar value or array of values).
Function AddFilterField(ByVal obj As Object, ByVal field, ByVal operator, Optional ByVal value) As Boolean
Dim oDBRange As Object, oRange As Object, oFilterDesc As Object
Dim s As String, ind as Long, i as Long, j As Long, lb as Long, filterFields, values, v
Dim tableFilterField As New com.sun.star.sheet.TableFilterField3
Dim filterFieldValues(0) As New com.sun.star.sheet.FilterFieldValue
Dim filterFieldValue As New com.sun.star.sheet.FilterFieldValue
Dim filterFieldsN(0) As New com.sun.star.sheet.TableFilterField3
AddFilterField=False
If HasUnoInterfaces(obj, "com.sun.star.sheet.XDatabaseRange") Then
oDBRange=obj
ElseIf HasUnoInterfaces(obj, "com.sun.star.sheet.XSpreadsheet") Then
oDBRange=GetSheetFilterDBRange(obj)
EndIf
If oDBRange Is Nothing Then Exit Function
If Not oDBRange.AutoFilter Then Exit Function
oRange=oDBRange.ReferredCells
If VarType(field)=V_STRING Then ' field name - search in the first row of oRangе
s=Lcase(field)
field=-1
For j=0 To oRange.Columns.Count - 1
If lCase(oRange.getCellByPosition(j, 0).string)=s Then
field=j
Exit For
End If
Next j
End If
If field<0 Then Exit Function
oFilterDesc=oDBRange.getFilterDescriptor()
filterFields=oFilterDesc.filterFields3
' The Values.FilterType property must be set.
' See: https://forum.openoffice.org/en/forum/viewtopic.php?t=109513&hilit=setFilterFields3 by @wrighch
For j=0 To UBound(filterFields)
tableFilterField=filterFields(j)
values=tableFilterField.values
For i=0 To Ubound(values)
filterFieldValue=values(i)
If filterFieldValue.StringValue<>"" And filterFieldValue.NumericValue=0 Then
filterFieldValue.FilterType=1 ' com.sun.star.sheet.FilterFieldType.STRING
Else
filterFieldValue.FilterType=0 ' com.sun.star.sheet.FilterFieldType.NUMERIC
End If
values(i)= filterFieldValue
Next i
tableFilterField.values=values
filterFields(j)=tableFilterField
Next j
' Define ind - the index of field in filterFields array
ind=-1
For j=0 To UBound(filterFields)
If filterFields(j).Field=field Then
ind=j
Exit For
End If
Next j
If operator=-1 Then ' remove filter field
If ind=-1 Then Exit Function
If UBound(filterFields)=0 Then
oFilterDesc.setFilterFields Array()
Else
For j=ind To UBound(filterFields)-1
filterFields(j)=filterFields(j+1)
Next j
ReDim Preserve filterFields(UBound(filterFields)-1)
oFilterDesc.setFilterFields3 filterFields
End If
oDBRange.refresh()
AddFilterField=True
Exit Function
End If ' operator=-1
tableFilterField.Operator=operator
tableFilterField.Field=field
If Not IsArray(value) Then value=Array(value)
lb=LBound(value)
ReDim filterFieldValues(Ubound(value) - lb)
For j=lb To Ubound(value)
If varType(value(j))=V_STRING Then
filterFieldValue.FilterType=1
filterFieldValue.StringValue=value(j)
Else
filterFieldValue.NumericValue=value(j)
filterFieldValue.FilterType=0
End If
filterFieldValues(j-lb)=filterFieldValue
Next j
tableFilterField.Values=filterFieldValues
If ind=-1 Then
If Ubound(filterFields)=-1 Then
filterFields=filterFieldsN
Else
Redim Preserve filterFields(0 To Ubound(filterFields)+1)
End If
ind=Ubound(filterFields)
End If
filterFields(ind)=tableFilterField
oFilterDesc.FilterFields3=filterFields
oDBRange.refresh()
AddFilterField=True
End Function
' -------------------------------------------------------------------------------------------------
' lang:en
' Returns the DatabaseRange object for sheet autofilter.
Function GetSheetFilterDBRange(oSheet) as Object
Dim i as Long, oDoc
GetSheetFilterDBRange=Nothing
oDoc=oSheet.Drawpage.Forms.Parent
i=oSheet.RangeAddress.Sheet
With oDoc.getPropertyValue("UnnamedDatabaseRanges")
If .hasByTable(i) Then GetSheetFilterDBRange=.getByTable(i)
End With
End Function
1 Like
Calc’s filtering and sorting is limited. I ge better result more easily from a database connection.
Something like the following is impossible or very hard to do in Calc, with or without macros:
OR before AND: SELECT * FROM TBL WHERE (A = B OR A=C) AND (B=D OR B=E)
Returnn 3 columns filtered and sorted by other columns: SELECT E,C,A FROM TBL WHERE B<1 ORDER BY F
There are no limitations on how many columns can be sorted and filtered, even when the database is a spreadsheet actually.
WIth a true relational database we have many more options because we can filter and sort one table by values in a related table.
We get much better performance after saving spreadsheet lists as *.dbf files, connecting a Base document to the dbf directory and adding some indices. Such a dBase connection works fluently with millions of records.
1 Like
Yes, that’s all true.
Nevertheless, millions of people use spreadsheets and we, to the best of our ability, try to help them.
1 Like
Well, no reasoning is strong enough to pull people away from silly spreadsheets. However, Base can add some advanced filtering and sorting to spreadsheets. Database links between different spreadsheet documents have some advantages over DDE and file:// links if the source data are consistatnt lists (one header row with consistant field types below).
1 Like
A different context is more familiar to me.
We, in particular, deal with corporate reporting. The data source is SQL databases (Oracle, PostgreSQL), reports are generated in Calc or Excel.
1 Like
This way you never encounter any difficulties with complex sorting and filtering. The spreadsheet is just a formattable and printable output medium with arithmetic extra features. You rarely have to write any bullshit like IF(A2="";"";B2/A2) either because there can not be any null value or because any operation with a null value gives null. And you never need to test if some value is consistent or not because the database engine takes care of it.
2 Likes
Yes, forum participants introduce me to these kinds of problems.
I even learned to use a compass and ruler to compose non-trivial formulas. 
1 Like
Ciao sokol92, ciao Villeroy… ciao everybody.
Thanks for spending time for my problem.
I say wow… 
… I never think that this could be a problem of this entity.
So, I will make some trys… let me some time for this and even it works or not, I will let know.
Because i say it sincerly, for me it is not so simple to understand what you wrote (I have very basic knowledge of programming in basic, that’s why poeple like me uses macrorecorder 
). But I will commit and try to do my best.
Thank you again.
1 Like