Ask Your Question
0

LibreOffice Calc: Filter via Macro?.. [closed]

asked 2015-02-12 04:57:45 +0200

darkpenguin gravatar image

I want a macro to filter a range looking for lines that contain a certain substring.

The substring I'm looking for is stored in a certain cell. I would prefer to store it in a TextBox, but apparenty there's no way to access the contents of a TextBox from a macro without linking it to a cell and reading from that cell. I've seen that question asked several times, but the only answer was "Just link it to a cell and read from that cell and be happy with that".

QUESTION 1. It it really possible to read directly from a TextBox placed on the spreadsheet, without linking it to a cell? I've tried to "Record a macro" (in order to see at least something interesting) and modify the contents of the TextBox, but that change was completely IGNORED, even when the TextBox WAS linked to a cell!!.. I thought at least the changes in the cell would be recorded...

QUESTION 2. What kind of "search operator" should I use to look for lines that "CONTAIN" the substring ?..

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-15 21:00:49.669535

1 Answer

Sort by » oldest newest most voted
0

answered 2015-02-12 09:53:28 +0200

pierre-yves samyn gravatar image

updated 2015-02-12 09:55:42 +0200

Hi - you can access to the content of a TextBox and use it to filter.

FR.MACROS_Calc_008_FiltrerPlages.ods

sub FiltrerPlage()

dim oSheet as object, oCtrl as object

dim oDataRange as object, oFiltre as object
dim oFilterField(0) As New com.sun.star.sheet.TableFilterField   

oSheet = thiscomponent.sheets.getByName("Sheet1")
oCtrl = oSheet.drawPage.Forms.getByName("Form").getByName("Text Box 1")

oDataRange = oSheet.getCellRangeByName("A1:F16")
oFiltre = oDataRange.createFilterDescriptor(true)
oDataRange.filter(oFiltre)  'Apply empty filter to reinitilize

with oFiltre                                    
    .ContainsHeader = true                      
'   .CopyOutputData = true                      'to copy result eleswhere
                                                'where to copy
'   .OutputPosition = oTargetSheet.getCellRangeByName("A3").CellAddress
    .UseRegularExpressions = true               'Use Regular expression
end with

with oFilterField(0)
    .Field = 0                  'Filter Col A
    .IsNumeric = false          'Use String, not a number
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
    .StringValue = ".*" & oCtrl.text & ".*"  
end with    

oFiltre.setFilterFields(oFilterField()) 
oDataRange.filter(oFiltre)

end sub
edit flag offensive delete link more

Comments

Oh! Thank you!

So, in one line (to see the full object path), the contents of a textbox are:

ThisComponent.Sheets.getByName("Sheet1").drawPage.Forms.getByName("Form").getByName("TextBox1").text

that looks scary. :)

So, the only way to do a text match is by EQUAL or using regexp, is that correct?.. I suck at regexp, so I wanted to try to avoid using them. :-P

darkpenguin gravatar imagedarkpenguin ( 2015-02-12 20:59:47 +0200 )edit

I would not say scary. Document can include multiple sheets, each of which can include several forms in which may include several "controls". The path reflects that tree. But I realize respond too seriously perhaps a dash of humor? Sorry if that's the case, I do not master English :) The list of operators here: API Reference That said the example shows how to deal with "CONTAIN"

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-02-13 17:01:22 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2015-02-12 04:57:45 +0200

Seen: 3,055 times

Last updated: Feb 12 '15