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

English
, ,
#1

First time posting here. I have been down da rabbit hole for the past few days, very exciting, advance filters and macor magic. I have an advanced filter in a named range on one sheet. It filters another named range on another sheet. If I apply the advance filter via the menu data > more filters > advanced filters, there is an option to turn on regular expression and when I do that it works fine. When I use my macro, the regular expressions are not turned on. They are positively frigid. How do I turn on the regular expressions in my macro? My macro is a mutation of the one on page 512 here https://www.pitonyak.org/OOME_3_0.pdf (open office macro’s explained by Andrew Pitonyak)

Sub AdvancedRangeFilter()
Dim oSheet 'A sheet from the Calc document.
Dim oRanges 'The NamedRanges property.
Dim oCritRange 'Range that contains the filter criteria.
Dim oDataRange 'Range that contains the data to filter.
Dim oFiltDesc 'Filter descriptor.

REM Range that contains the filter criteria

oSheet = ThisComponent.Sheets.getByName(“sheetFiltersAreHere”) REM the filters are on this sheet
oCritRange = oSheet.getCellRangeByName(“FilterByMe”) Rem the filters are in a named range and expression

REM The data that you want to filter
oSheet = ThisComponent.Sheets.getByName(“ToBeFilteredIam”)
oDataRange = oSheet.getCellRangeByName(“RangeToBeFiltered”)

oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
oDataRange.filter(oFiltDesc)
End Sub

1 Like
#2

Reminder: LO comes with a database component which can can handle a lot more filtering and sorting asby means of queries without a single line of stupid Basic code. Filtered and sorted database data are accessible from Calc and Writer.

In Calc you should not filter/sort named ranges. Use database ranges instead. A database range stores filters and sort orders. menu:Data>Refresh refreshes the current sort/filter after your data have changed.

For the criteria range of an “advanced” filter (a bad joke compared with database queries) you may use a named range together with the “Filter” flag, so it appears in the listbox of the “advanced” filter dialog.

http://forum.openoffice.org/en/forum/download/file.php?id=136 (16 year old stupid Basic code refreshing a database range filtered by “advanced” filtering)

How attribute personalized filters to objects/buttons?
#4

typefilter.ods (42.2 KB)

#5

A very elegant solution, not at all “stupid”. The search string “myri|ern|nth” instantly selected cells with the words “Myriam”, “Bernard” and “Günther” - as required.

#6

I would very much like to simply turn on the regex expression, without typing anyting as the advance filter is happily sitting there, raring to go. Maybe something like this OpenOffice Calc regex matching macro · GitHub couyld manage it.

it does nothing in my code though. Is that because it is specific to text I wonder.

Sub AdvancedFilterDaSummarySheets()
Dim oSheet 'A sheet from the Calc document.
Dim oRanges 'The NamedRanges property.
Dim oCritRange 'Range that contains the filter criteria.
Dim oDataRange 'Range that contains the data to filter.
Dim oFiltDesc 'Filter descriptor.

’ prepare regexp search options
oTextSearch = CreateUnoService(“com.sun.star.util.TextSearch”)
oOptions = CreateUnoStruct(“com.sun.star.util.SearchOptions”)
oOptions.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
oOptions.searchString = b
oTextSearch.setOptions(oOptions)

REM Range that contains the filter criteria

oSheet = ThisComponent.Sheets.getByName(“filters”)
oCritRange = oOptions
oCritRange = oSheet.getCellRangeByName(“aaLarge”)

REM The data that you want to filter
oSheet = ThisComponent.Sheets.getByName(“Large”)
oDataRange = oSheet.getCellRangeByName(“Large”)

oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
oDataRange.filter(oFiltDesc)

End Sub

#7

Without the MRI extension (or Xray) you can’t get your head around this API. Install MRI (or Xray) and learn how to make use of it. Meanwhile you can pre-configure all these complex details in your database range as demonstrated in my sample document.

No matter how much stupid Basic code you throw at your spreadsheets, they will never come close to a simple database solution…

#8

oooh, interesting, is the MRI extension like script forge? I tried to load scriptforge but it came up with a basic runtime error.

#9

Forget Script Forge. I have never seen any solution based on this.

https://forum.openoffice.org/en/forum/viewtopic.php?t=49294
Code generated by MRI:

Sub Snippet
  Dim oDatabaseRanges As Variant
  Dim oObj1 As Variant
  Dim oFilterDescriptor As Variant
  Dim bContainsHeader As Boolean
  Dim bCopyOutputData As Boolean
  Dim bIsCaseSensitive As Boolean
  Dim nMaxFieldCount As Long
  Dim aOutputPosition As New com.sun.star.table.CellAddress
  Dim bUseRegularExpressions As Boolean

  oDatabaseRanges = ThisComponent.DatabaseRanges
  oObj1 = oDatabaseRanges.getByName("Import1")
  oFilterDescriptor = oObj1.getFilterDescriptor()
  
  bContainsHeader = oFilterDescriptor.ContainsHeader
  bCopyOutputData = oFilterDescriptor.CopyOutputData
  bIsCaseSensitive = oFilterDescriptor.IsCaseSensitive
  
  nMaxFieldCount = oFilterDescriptor.MaxFieldCount
  aOutputPosition = oFilterDescriptor.OutputPosition
  bUseRegularExpressions = oFilterDescriptor.UseRegularExpressions
  
End Sub
1 Like
#10

awesome. Very inspiring. Trying to get my head around it. It’s to do with the
rem createFilterDescriptorByObject creates a filter descriptor for the specified filterable object from the contents of this object

I wonder if I can canablise stuff from here: Filtering Multiple Columns and Filtering with Regular Expressions - The Document Foundation Wiki

Sub SimpleRangeFilter()
  Dim oSheet          ' Sheet to filter.
  Dim oRange          ' Range to be filtered.
  Dim oFilterDesc     ' Filter descriptor.
  Dim oFields(1) As New com.sun.star.sheet.TableFilterField
  
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oRange = oSheet.getCellRangeByName("A1:H11")
  
  REM If argument is True, creates an 
  REM empty filter descriptor.
  oFilterDesc = oRange.createFilterDescriptor(True)

  REM Setup a field to view cells with content that 
  REM start with the letter B.
  With oFields(0)   
    .Field = 0              ' Filter column A (Student names).
    .IsNumeric = False      ' Use a string, not a number.
    .StringValue = "b.*"    ' Every name starting with a B.
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  End With
  REM Set up a field that requires at least one of the conditions.
  REM This new condition requires a value less than or 
  REM equal to 90.
  With oFields(1)
    .Connection = com.sun.star.sheet.FilterConnection.OR
    .Field = 6              ' Filter column G (Test #1 grades).
    .IsNumeric = True       ' Use a number
    .NumericValue = 90      ' Scores less than 90
    .Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
  End With

  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = True
  oFilterDesc.UseRegularExpressions = True
  oRange.filter(oFilterDesc)
End Sub
#11

It was just this trickly little tickle of code, and then it works! Yay!

oFiltDesc.UseRegularExpressions = True

Sub AdvancedRangeFilterV5()
Dim oSheet     'A sheet from the Calc document.
Dim oRanges    'The NamedRanges property.
Dim oCritRange 'Range that contains the filter criteria.
Dim oDataRange 'Range that contains the data to filter.
Dim oFiltDesc  'Filter descriptor.

REM Range that contains the filter criteria

oSheet = ThisComponent.Sheets.getByName("filters")
oCritRange = oSheet.getCellRangeByName("aaLarge")

REM The data that you want to filter
oSheet = ThisComponent.Sheets.getByName("Large")
oDataRange = oSheet.getCellRangeByName("Large")


oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
oFiltDesc.UseRegularExpressions = True
oDataRange.filter(oFiltDesc)

End Sub```
1 Like
#12

yes ___________: +1

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