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 !!!
************** 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.)