Macro Automatic filter or Standard filter in Calc

English
,
#1

Hi, I am trying to create an automatic filter with only one criteria. An “advanced filter” doesn’t work for me.
In Mauricio’s book “Aprendiendo OOo Basic” book link: link text
There is a macro to create an automatic filter and filter the table by a single column with a single criteria.
But when testing it, I have only managed to activate the filter but no result appears.

When checking if the macro takes the variables, I could see in Menu / Data / More filters / ‘Standard Filter’ that the name by which I want to filter is in this filter (see the image Photo1)
So I don’t understand what is wrong.

Can someone run it and see what happens? I have put the macro code below and attached the file as well.
Thank you.
P.S. the variable sName = “Direcciones” I defined the range in Menu / Data / define range / Name written ‘Direcciones’ and select the entire table with headers included to make it work, otherwise it does not find the range.

Look at the photos of what appears after taking the macro.
Thanks a lot !!!

C:\fakepath\Photo1.jpg

C:\fakepath\Photo2.jpg

************** MACRO STARTS HERE **********************

Sub FiltroAutomatico()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField

 oDoc = ThisComponent
 oRangosBD = oDoc.DataBaseRanges()
 sNombre = "Direcciones" 'En Menu/Datos/definir intervalo/Nombre escribir 'Direcciones' y seleccionar toda la tabla con encabezados incluidos.
 If oRangosBD.hasByName( sNombre ) Then 'Verificamos que exista el nombre del rango de datos
     oRBD = oRangosBD.getByName( sNombre )
     
     'Mostramos el autofiltro
     oRBD.AutoFilter = True 'Pone flecha a las columnas del filtro.
           
     oDesFiltro = oRBD.getFilterDescriptor()
     'El campo por el que queremos filtrar
     mCamposFiltro(0).Field = 1 'Columnas empiezan a partir de la 0. El 1 significa que buscara en la columna 2.
     'El tipo de comparación
     mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
     'Si es un número
     mCamposFiltro(0).IsNumeric = False
     'El valor de comparación
     mCamposFiltro(0).StringValue = "nikole"
     'Le pasamos los campos
     oDesFiltro.FilterFields = mCamposFiltro
     'Refrescamos el rango para ver el resultado del filtro
     oRBD.refresh()
 Else
     MsgBox "El rango de datos no existe"
 End If

End Sub

Filtro automatico con macro - Test.ods

(edited by the questioner to make photos visible)
(Slightly edited by @Lupp for better readability of the code.)

#2

Hello @JoePublic, Please, if your question is only about Calc, choose retag (below your question), delete writer, and press Enter twice. Thanks.

#3

I don’t understand what not work. I attach file with code that work fine.

image description

ask.ods

P.D. I need rewrite my book for LibreOffice :slight_smile:

#4

Mauricio, your macro works perfectly and I deleted my Sheet1 and paste the table in a new Sheet2 and now it works fine. I don’t know what was wrong with my file.
So you don’t need to change anything from the book it is ok.

Mauricio, I want to express my gratitude for the effort you have put into writing this guidebook for making macros with LibreOffice and Calc. Thank you with all my heart. The libreoffice macros are very peculiar and sometimes difficult. I do not understand how the creators of LibreOffice do not facilitate the creation of macros by changing the language of OOoBasic and using javascript or python or any other easier language and at the same time adding USEFUL, PRACTICAL and well-indexed guides with examples so that people can filter by several words at the same time and get a quick response from each function, method, macro, etc. I know all is free, but OOo Basic to me is very unconfortable & awful language in comparison with VBA.

#5

In my opinion it is the only thing missing from the wonderful LIBREOFFICE. Thank you for your attached file C:\fakepath\ask.ods

#6

Mauricio, Referente al punto 6.7 de tu libro “Rango de datos” yo explicaría un poco mas en este apartado la diferencia entre nombres de “Rango de datos” y Nombres de “Rango de Celdas”. (Yo en este apartado me perdí y no entendía y viendo donde esta en el menu y viendo algun ejemplo practico uno entiende más). Así que para ello añadí a tu párrafo esta breve explicación a la que le falta tu supervisión.

6.7 Rango de datos
Los rangos de datos, son áreas rectangulares de rangos de celdas delimitados por al menos una fila y una columna en blanco a las cuales se les establece un nombre y tienen características especiales que facilitan su uso como si de una base de datos se tratara. Ya que con ellos se pueden utilizar para… (Escribir algun ejemplo breve de para que se utilizan los nombres de rango de datos, por ejemplo para ejecutar macros para Autofiltrar una tabla con datos).

Para definir un rango de datos … (continua abajo)

#7

Para definir un rango de datos ve al Menu/Datos/Definir Intervalo. Añadir un nombre al rango de datos y selecciona la tabla con los datos incluido el encabezado.(Este lo puedes poner tambien en el punto 6.7.1 Definiendo rangos)

No confundas estos nombres, con los nombres que puedes definir en el “cuadro de nombres” de la barra de formulas, ya que son distintos y se llaman “Rangos de celdas” y se asignan a celdas y rangos de celda. Vease Menu/Hoja/Intervalos y expresiones con nombres… (Escribir algun ejemplo breve de para que se utilizan los nombres de rangos de celda.)

#8

For those who are looking for the script-MACRO, Download the file C:\fakepath\ask.ods that MAURICIO attached in his answer.

#9

Just another simple, but complete example which should explain itself:

Sub filterExample()
doc0 = ThisComponent
sSheet1 = doc0.Sheets(0)

rg = sSheet1.getCellRangeByName("E2:F1001")
fiDesc = rg.createFilterDescriptor(True)

Dim fiFields2(1) As New com.sun.star.sheet.TableFilterField2
REM TableFilterFields2 is an enhanced version offering additional operators.

With fiFields2(0)
  .Connection = 0 REM Default meaning AND.
  .Field = 1 REM SECOND  column of the range. Column F here
  .Operator = 2 REM Equal
  .IsNumeric = False REM The .StringValue is used
  .NumericValue = 0 REM default, Obsolete here.
  .StringValue = "A"
End With

With fiFields2(1)
  .Connection = 0 REM Default meaning AND.
  .Field = 0 REM FIRST  column of the range. Column E here
  .Operator = 3 REM NotEqual
  .IsNumeric = False REM The .StringValue is used
  .NumericValue = 0 REM default, Obsolete here.
  .StringValue = "1"
End With

fiDesc.setFilterFields2(fiFields2)
rg.filter(fiDesc)
End Sub

See ask272144customFilter.ods
(Added regarding a comment below): ask272144customFilter_2.ods

By the way: It may not exactly be useful to write Basic code using non-English words as the background of chosen names. You need to know related English words (for API items e.g.) anyway.

===Editing===
I missed to mention that the AutoFilter isn’t a filter like the others, but needs to be applied to to a DatabaseRange by setting its respective property to True newly. The code contained in the attachment to the question seemed to show this was known.
===Editing 2020-10-19 about 21:30UTC===
The announced piece of code:

Sub autoFilterExample()
doc0 = ThisComponent
sheet1 = doc0.Sheets(0)
rgWithHeaders = sheet1.getCellRangeByName("M1:N1001")
cCtrl = doc0.CurrentController
oldSel = doc0.CurrentSelection REM May be obsolete
REM The dispatcher command works on the current selection:  
cCtrl.select(rgWithHeaders)
dispFrame = ThisComponent.CurrentController.Frame
dispHelp = createUnoService("com.sun.star.frame.DispatchHelper")
dispHelp.executeDispatch(dispFrame, ".uno:DataFilterAutoFilter", "", 0, Array())
cCtrl.select(oldSel) REM May be obsolete.
End Sub
#10

Not, variables in code Basic work perfectly with not-English words

#11

Of course! What’s accepted as a name in Basic is -as far as I understand it- exclusively determined by the formal syntax which can be expressed by the RegEx [A-Za-z_][0-9A-Za-z_]*, and by the interdiction of predefined “symbols” (FOR e.g.) which may also look like names.
My incidental comment only tried to suggest considerations concerning advantages and disadvantages of “localizing” names in a context where fix non-localized names must be used in many places (API in specific, but also the words used as symbols by Basic itself).
My personal decision is to not use German words to base user-chosen names on in the context of programming.

#12

Thanks a lot for your script and for your advice !!!
Your filter works perfect… but I would like to show the Autofilter in columns header. I would like to see the arrows > in the headers like Autofilter does, when you press it from Menu. Can you add that to your macro too, please? Thanks

#13

As I already told editing my answer, the AutoFilter is the one filter not being based on a FilterDescriptor, and therefore not being applicable the same way as other filters to any SheetCellRange.
The only way I know to create an AutoFilter via the API is to create a DatabaseRange and to set its .Autofilter property to TRUE.
The dispatcher command .uno:DataFilterAutoFilter seems to know a different way.
I would suggest to use the dispatcher therefore, and will append a respective example Sub to my answer.

#14

Hi Lupp,
Where can I paste your code Sub autoFilterExample() ?
I am a newbie. And I can code VBA just a little. Why don’t post the macro completey?.
Thank you.

#15

I posted the macro applying an AutoFilter completely! It’s just not contained in the example I attached to my answer above. You may paste it into the used module there.
If you actually want to provide the functionality added the macro-do not just on a per-document basis, but for all your documents, you would need anyway to create a module in your local Standard library inside of the My Macros container, and to paste the macro(s) there.

In addition you will have noticed that the macros I providd are traw examples with hard-coded ranges and field settings. To make them actually useful, you need to find/use a way to pass arguments to the Sub in every case. Otherwis you need to make a specialized version for every slightly different case.
It’s not without a reason that the tool Standard Filter uses a dialog to get the needed information, and that there is the variant Advanced Filter, offering a way to pass parameters without user interaction at runtime.

#16

Lupp, thank you for your explanation and thank you for be so patient with me. Both macros works and I set both in the same Module, but I am not a programmer and I only know a few things about OOO basic. I can’t do what you explain me … “to find the way to pass arguments to the Sub”, because there are many sentences of your script that I don’t understand.

I started learning VBA for Excel and later I started with Libo macros. Some things are the same as VBA, but others are really complicated to me to understand. I don’t feel comfortable with OOo Basic and to me it is a great effort as “amateur” struggling with functions and methods that I don’t understand.
I usually learn reading script examples in OOO basic, but it takes me a lot to find examples on the Internet that cover my needs and beside that, there isn’t so much help for LibreOfiice’s macros as for VBA Excel.

If you could send me an example using both macros I would be very gratefull.
Thank you.
Auf jedenfall Vielen Dank !

#17

A simple example also containing the second macro was attached to my answer. when my above comment was finished.
I would suppose your words about “OOo Basic” are actually addressing the usage of the LibreOffice API. The fundamental difference between MA VBA versions, and user code for LibreOffice is that VBA is a programming language and an API at the same time. This is seen as an advantage by some users, but it comes with significant disadvantages too. Just my opinion: From the point of view of MS it’s a tool to reinforce incompatibility with the software by competitors, and by that the user lock-in they depend on.
The “argument thing” would roughly be the same with VBA.
You may try to simply replace the hard-coded range names and some of the property values of the standard filter and its fields by the ones you prefer.
See also: LibreOffice: TableFilterField Struct Reference

#18

Hi Lupp
Thank you for the link.
I replaced the range names and some of the property values of the standard filter and its fields by the ones I prefer and the macro works pretty well, but some rows disappear or hide when the filter is applied, as well as the first row (where the header is) and the arrows of the autofilter.

Do you know how could I apply the filter wihout hidding the first row?. I have a table of 10 columns and the filter criteria is only for two columns, How could I see in the first row (headers with Name of columns) the arrows with a point that represent that the filter has been aplied only in this columns?. That’s the ONLY thing I need to know.

With your script-macro: “filterExample” the table is filtered and disapper rows that doesn’t match my criteria, even the first row disappear (headers).
With macro: autoFilterExample() appear the rows for Autofilter at the first row of my table, but later disappear when applying “filterExample”.
Sorry for botherin you

#19

Hallo LUPP,
I misread your last comentary and didn’t pick up your file C:\fakepath\ask272144customFilter_2.ods

I realized today and checked this file with your new macro. I run it but, Unfortunatelly It displays an Error Window saying:

"Inadmissible value or data type. Index out of defined range."

The macro stop at line 6:

dbRg1 = doc0.DatabaseRanges(0)

Can you check it please? I would be very grateful if this work !!!

#20

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.