Calc: Howto create unique list in macro using standard filter

[kribjo]

Hi,

I have two columns in Calc with 11 rows including header. It’s used as a reimbursement form for expenses.
Colums are Amount and Category. I want it to automatically sum up each category. This is easily done creating to new columns, one with categories without duplicates created using standard filter (I have created a range for Category not including header and using it in filter), and one with sumif.

But when I try do to this in a macro I cannot get it to work. I realize I must do something wrong, but I cannot figure out what. I have done some reading on google searches and in this forum, but I’m hoping this post will help.

Looking av the BASIC code created I observe that StandardFilter is not executed, because it’s commented out. I don’t understand what I’m doing wrong.

Code is looking like this:

REM  *****  BASIC  *****


sub Main
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 ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterStandardFilter", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$B$7"

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$B$4"

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

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

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


end sub

As I’m a screenshot junkie I would prefer to add screenshots, but I don’t have enough points in the forum yet.
Appreciate the help of the community. Thanks.

Regards, Bjørn

[Lupp]

A screenshot would be no use. A relevant sample document might be useful.
You won’t get anywhere with this task based on recorded macros, IMO.

[kribjo]

Thanks for feedback Lupp. I don’t have enough point to attach files, so I’ll link to them below. I don’t believe that I’ll get anywhere with the macro as it is now. But I’m not a programmer, nor do I know the LibreOffice API or uno. Hence macros makes it possible.

Standard Filter manually.ods
Standard Filter macro.ods

[Lupp]

I know standard filters and I know a bit about how to use the uno-API (in LibO BASIC).
“Hence macros makes it possible.” (?) Good luck! Better read the famous texts by Andrew Pitonyak.
(My dislike of screenshots was strengthened again.)

[kribjo]

Starting to realize that recording macros might not be optimal in some situations. I’ll follow your lead and read me up on uno-API and look at Andrew Pitonyak’s examples. Thanks.

[Lupp]

If I understood correctly, you allow for arbitrarily typed categories?
If you do so, any typo or even a trailing space will spoil the summation by categories. Why don’t you use a well defined table of categories which you can easily maintain and sort differently if needed?
Based on a predefined table you may used the ‘Data’ > ‘Validity’ tool for entering categories. Typing free, a conditional format can raise an alert (colouring the respective cell, e.g.) if an unknown category is entered. You may then decide to change the entry or to add it to the table of allowed categories.

[kribjo]

Category should only be a number. I agree that validation should be used, and I will at a later stage, a combination of vlookup and validation. But at the moment I would like to fix my issue with Standard Filter in Macro and then move on.