LibreOffice Calc: Filter via Macro?

[darkpenguin]

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 ?..

[PYS]

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

[darkpenguin]

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.

[PYS]

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”