Ask Your Question
0

LibreOffice Calc: Filter via Macro?..

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 close merge delete

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 2,295 times

Last updated: Feb 12 '15