Help to create a Filter & Restore Sheet macro's

[rde]

I have just migrated to Calc and I have spend days - without success, trying to create 2 filter macro’s in Calc that I used to use in Excel. I am hoping that someone can help me with this.

I have a table with a range of B9:AAZ5000 (row 9 is the header row).
However, for this filter task only text in the range D10:D5000 is searched - based on the text search criteria in cell D6.

What each macro needs to do:

Filter Macro

1. Clear cell D6
2. Paste the clipboard to cell D6 (D6 being the Search Criteria)
3. Filter the range D10:D5000 based the cell D6 Search Criteria (this results in only rows with matching text in D6 and range D10:D5000 being displayed)

Restore Sheet

1. Clear the search criteria in cell D6
2. Clear the filter and/or
3. Unhide/Show the hidden rows caused by the filter macro above
4. Return the cursor to Cell A10

I am really hoping someone can help me with these macro, as I am getting no where on my own.
Thank you in advance

[Villeroy]

ask132662.ods (60.2 KB)

[sokol92]

See also this topic.

[rde]

Hi Gilberto, thank you for your solution. I really appreciate your time.
I have applied your macro’s to my spreadsheet and it functions as per your example.
Here is what I have noted from your Macro’s

1. The Filter and Clear macros row by row search is very slow, about .5 sec per row. So over 5000 rows it takes each about 3 minutes to filter. This is not practical if 20 or 30 searches ned to be done.
   The type of behaviour I am hoping for is like the speed of the Standard Filter, which is an instant filter. Is it possible to create a macro that functions like the Standard Filter?

2. The HideRowsColumnD macro doesn’t include the first step of Pasting the Clipboard into cell D6

3. The ShowLinesClear macro doesn’t include the step of Clearing cell D6 and the last step of returning the cursor to cell A10. I think that this can be achieved with

Sub DeleteD6
ThisComponent.Sheets(0).getCellRangeByName(“D6”).setString("")
End Sub

and (a recorded Macro)

sub CursorA10
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = “ToPoint”
args1(0).Value = “$Orders.$A$10”

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args1())

end sub

I hope that my reply is understandable and that solutions to my observations are possible?

[rde]

Hi Villeroy, thank you for your .ods
I have had look at the spreadsheet. It looks positive for what I am trying to achieve.
I will try and study what you have created to see if I can use this in my spreadsheet.
I will let you know how I get on
Thank you

[rde]

Hi Villeroy,
I think that your solution could be a good way to go for me. I am trying to work out how to implement it in my spreadsheet.

What I have done so far:
~I have transferred your macros to my spreadsheet
~I have set a Standard Filter row
~I have added the cells in your spreadsheet D1:D2, E1:E2, F1:F2 to my spreadsheet, including the formula’s in row 2
~I have inserted a shape to use as the Search Criteria box (“Type here>>>” on your spreadsheet.

I am not sure if I am setting this up correctly and I am hoping that you cold help guide me?

For example, can you help me with the “Type here>>” search criteria box?
I had something similar in my Excel version of my spreadsheet but I can’t remember how I created it.
~ Is this a named shape? I have created a shape and named it “Data”.
~How did you link this shape with the cell named “linked_cell” in D2
~How does this activate your filter macros?

I have tried attaching a screenshot to this message as a reference, but as a New User it seems that I am not allowed to do this. I also cannot message you directly so I hope that you receive this message.

Thank you again for your help.

[sokol92]

The LibreOffice Calc object model is different from the Microsoft Excel object model, and a comparative study requires time.
In your example, an AutoFilter is set in Excel using a macro.
Just like in Excel (Worksheet), Calc Spreadsheet can only have one AutoFilter. Additionally, some objects have their own AutoFilter. These include pivot tables (Calc, Excel), Database ranges (Calc), and ListObjects (Excel).
In addition to AutoFilters, Excel and Calc also have Advanced filters. In @Villeroy’s example, an advanced filter is set.

[rde]

I have obviously been Americanised by Microsoft!

Thank you for the information on your message.

[Villeroy]

It’s a form control. A form control can be bound to a field of a database’s record set or to a spreadsheet’s single cell.
Open my document.
View>Tollbars>Form Controls
Turn on design mode, select the form control and get its properties.
It has a linked cell and a “Text modified” event.
Add a text control to your own sheet while in design mode.
The filter in my sample makes use of regular expressions in order to find strings contained in the given search string. You may link your control directly to the criteria cell F2 in my sample in order to search for the literal string.

[Villeroy]

And this is what you asked for. It does not take any care about the actual clipboard content!
Add the module to my demo document and run applyFilter or clearFilter.
In your own document, adjust the 3 constants on top.

REM  *****  BASIC  ***** 
REM database range to be filtered
Const cDBRange = "Data"
REM cell getting the criterion from clipboard
Const cCell = "D2"
REM zero-based index of column to be filtered by criterion
Const cColIndex = 1 

Sub applyFilter()
	setFilter bClear:=False
End Sub

Sub clearFilter()
	setFilter bClear:=True
End Sub

Sub setFilter(bClear As Boolean)
	dbr = ThisComponent.DatabaseRanges.getByName(cDBRange)
	view = ThisComponent.getCurrentController()
	fd = dbr.getFilterDescriptor()
	rg = dbr.getReferredCells()
	cell = rg.Spreadsheet.getCellRangeByName(cCell)
	view.select(cell)
	if bClear then
		cell.setString("")
		aFF() = Array()
	Else
		dispatch_Paste
		v = cell.getString()
		ff = makeFilterField(cColIndex, com.sun.star.sheet.FilterOperator.EQUAL, False, v)
		aFF() = Array(ff)
	end if
	fd.setFilterFields(aFF())
	rg.filter(fd)
End Sub

function makeFilterField(iCol&, iOperator%,bNum As Boolean, val)
	ff = createUnoStruct("com.sun.star.sheet.TableFilterField")
	ff. isNumeric = False
	ff.Operator = iOperator
	ff.Field = iCol
	ff.StringValue = cStr(val)
	if bNum then 
		ff.NumericValue = cDbl(val)
	else
		ff.NumericValue = 0
	endif
	makeFilterField = ff
end function

sub dispatch_Paste()
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
end sub

[rde]

Hi Andreas

Thank you very much for your reply. You have excellent Office skills!

I am working through both of your messages. Your messages are reminding me of how I created the same spreadsheet in Excel about 5 years ago.

I will keep on working implementing your directions and I will let you know how I go!

[rde]

Hi Andreas

This script works perfectly in your demo .ods

Thank you very much. I am very impressed

First, I have to get your filters working in my document before I can apply and test this macro in my document. At the moment I am getting many errors as I update your filter macros in my document. So I haven’t been able to get the filters to function yet.

Kind regards

Richard

[Villeroy]

I use a database range instead of a range address. Add one via menu::Data>Define…
Have a look at the 3 constants on top of my module.

[rde]

Hi Andreas

Thank you once again for your reply.

I think that I have corrected the 3 constants and the Paste Clipboard and Clear the Form Control box macro is now functional!

Excellent, thank you.

But I cannot emulate your sample .ods document yet. That is,

1. I cannot manually type Search Criteria into the Form Control box and get a result
2. I cannot manually clear the Form Control box and have the filtered hidden rows restored.
3. Partial Names (e.g. Fred > Fre.*) do not return a result when entering data into the Form Control box via a Clipboard Paste or by Manually typing

I am not sure which of your macros this applies to and why it this functionality is broken in my document.

I have attached a version of the test document that show you where I am up to.

I will keep looking for why this isn’t working. But if you have any thoughts, then I would be glad to hear from you.

Thank you again.

Kind regards
Richard

Item Cost Test v1.1.ods (447 KB)

[rde]

Hi Andreas,

I have kept on testing by adding your Clipboard Paster / Filter / Clear macro to your original 132662.ods document.

This macro stopped the behaviour of the original macros (manually typing in the Search Criteria, including partial names and manually deleting the search criteria) in your ask132662.ods document.

Is it possible to have both methods of entering data and filtering the data? 1. Manually as per 132662.ods, 2. via the Clipboard Paste macro that was sent to me later?

[Villeroy]

Set “Regular expressions” in the advanced filter dialog. This setting is kept for other types of filters.
In addition, you may add a line to the “setFilter” routine:

	fd.setFilterFields(aFF())
-->	fd.UseRegularExpressions = True
	rg.filter(fd)
End Sub

[rde]

Hi Andreas

You are nothing short of amazing! You make it all seem so simple - when it is not.

I have made those changes and all is working perfectly now.

Thank you very much for your patience with me. I truly appreciate it

Let me know if I can send you some coffee money!

[schiavinatto]

rde_132662.ods (13,0,KB)