LibreOffice including a filter into macro

Question

I'm trying to do this for two days now: working on a Mac with LibreOffice 5.4.

I have CSV-file with a couple of appointments, dates etc. What I need this file to do on opening, is filtering for all the appointments of today. What I have so far is, that I check every line and add 1 to the last row if it matches or add a 0 if the day won't be today.

Actually I recorded my actions, so my current macro is not optimal, that's why I'm not posting it.

https://picload.org/thumbnail/rwwiiaaa/image.jpg here is a picture of a part of the table. this is my macro so far:

sub csvfilter
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 = "$AI$1"

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = "=TEXT(TODAY();"+CHR$(34)+"YYYY-MM-DD"+CHR$(34)+")"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "StringName"
args4(0).Value = "=IF(AI$1=LEFTB(C2;10);"+CHR$(34)+"1"+CHR$(34)+";"+CHR$(34)+"0"+CHR$(34)+")"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$AI$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

oSheet = thisComponent.sheets(0)
ocursor = oSheet.createCursor()
ocursor.gotoStart() 
ocursor.gotoEndofUsedArea(false)

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "EndCell"
args7(0).Value = "$AI" & ocursor.getRangeAddress.endRow+1

dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$AI$2:$AI$18"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$AI$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterStandardFilter", "", 0, Array())

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

My question is, how can I filter inside the macro for the 1s in the last row?

Answer 1

Recording is usually not a good way to learn or write macros. Instead, find examples and documentation online. Searching Google for openoffice macro filter brings up plenty of useful results.

Let's say the spreadsheet looks like this.

A   B   C
~~  ~~  ~~
1   A   AA
0   B   BB
0   C   CC
1   D   DD
1   E   EE
1   F   FF
0   G   GG
0   H   HH
1   I   II
0   J   JJ

Here is the macro to hide all rows except those where column A is 1, adapted from Listing 5 of the OpenOffice User Guide Filters page.

Sub SimpleSheetFilter()
  Dim oSheet  ' Sheet that will contain the filter.
  Dim oFilterDesc  ' Filter descriptor.
  Dim oFields(0) As New com.sun.star.sheet.TableFilterField
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oFilterDesc = oSheet.createFilterDescriptor(True)
  With oFields(0)
    .Field = 0  ' Column A
    .IsNumeric = True
    .NumericValue = 1
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  End With
  oFilterDesc.setFilterFields(oFields())
  oSheet.filter(oFilterDesc)
End Sub

The result: