macro to !modify! autofilter in calc II [solved]

[newbie-01]

hello all,

this is a cry for help, i’m stuck in a project that’s very important for me, and! i’m too short in time to learn all needed things ‘on the fly’.

• and the LO / OO scripting concept is too far from what i know from excel -

the problem is: i can only ‘set’ but not ‘modify’ the (auto)filter in a table via a macro.

• in excel i have direct access to the structure? and can record a macro modifying it, pimp it up a little and was done -
  i know there are structures like ‘XSheetFilterDescriptor’, ‘~2’, and ‘methods’ like ‘getFilterFileds?’ in OO and LO, what’s missing is how to get this together and working.
• i’m too short in OO, LO, object oriented programming and so on -
• i have! searched the web up and down, but to no avail -
• i got! help to !create! an autofilter structure, unfortunately all ideas rely on creating a ‘new’ filter, rather than modifying the existing -

one very simple example could help me out …

• have a table with a range with ‘autofilter’ on,
• have at least one column filtered,
• have a macro that:
• defines a variable for the filterstructure,
• reads the actual filter settings from the filter structure into this variable,
• modifies the value of the filter criteria into the variable,
• writes the variable back to the filtering structure for the range to take effect in the table,

as far as i have read that’s the way to do it, i’m just some steps below the skills about the correct coding and access to structures to write this by my own. so if i’m wrong in the idea above i’d like any correction.
the biggest step is to ‘read’ the values that are active within the range

• excel is easier, as i only want to modify one value i just need to define which one, and have write access to it -
  “ActiveSheet.Range(“data1”).AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Text”
• i’m absolutly not! a fan of microsoft - no no no - but that’s handy, easy and good -
  if something like that is possible in LO too … pls give me a hint.

i tried! to run the VBA code in LO, it does! work, but unfortunately it ‘wipes’ existing filter criteria for other columns when setting that for the actual one

!any help appreciated!

thanks,

newbie-01

[jimk]

Your questions are written in a way that is very difficult to answer. The LO / OO scripting concept is too far from what I know from Excel - Yes, which is why you need to tell us specifically what you want to do in LO, not how it works in Excel, because many of us do not use Excel. I’m stuck in a project that’s very important for me. We are volunteers, so it’s not polite to make demands. Having said that, questions asked on this site normally get answered quickly and comprehensively.

[jimk]

Most importantly, please follow up on your other question at macro to modify autofilter in calc. Lupp spent a lot of time working on a solution. Did it meet your needs? If it did partly, then upvote or write comments. Otherwise, it discourages anyone else from answering your questions. See guidelines for asking.

[jimk]

One more note: I realize I’ve been critical of your questions, but please recognize that you have not taken much time to learn how to use this site properly. Put in too little effort and you may be rewarded with poor results, but spend time asking a good question and you will typically get good answers. With some investment of effort, you can learn and then will receive more positive feedback from me.

[newbie-01]

hello Jim K,
thks for your hints,
i did! explain what i’d like to have, and gave! an example in excel beside my explanation for those whom my writing is too complicated,
i did! look and comment and answer in the other thread, alone only one answer per user is allowed …
if you look there you’ll see that i analysed the work from Lupp? - i thought it was librebel? - and found it has just that simple shortcoming, it doesn’t modify the filter but replaces it …
newbie-01

[newbie-01]

@jimk may it be that you just argued ‘against’ my questioning without having looked at the content of the - edited - question and answer in the other thread?
for anyone who’s frustrated about me talking about excel, take just the next comment and try to solve:

[newbie-01]

any other way to modify! a autofilter would be nice too …

• have a table with a range with ‘autofilter’ on,
• have at least one column filtered,
• have a macro that:
• defines a variable for the filterstructure,
• reads the actual filter settings from the filter structure into this variable,
• modifies the value of the filter criteria into the variable,
• writes the variable back to the filtering structure for the range to take effect in the table,

[jimk]

i thought it was librebel? My mistake. Since you’ve been responsive, I’ll work on an answer for you.

[jimk]

Here is code that does what you asked in the comment.

Sub ModifyFilter
    oDoc = ThisComponent
    oRanges = oDoc.getPropertyValue("DatabaseRanges")
    oRange = oRanges.getByName("data1")
    oFilterDesc = oRange.getFilterDescriptor()
    aFilterFields = oFilterDesc.getFilterFields()
    With aFilterFields(0)
        .StringValue = "b"
    End With
    oFilterDesc.setFilterFields(aFilterFields)
    oRange.refresh()
End Sub

Sample file: modify autofilter.ods

Documentation is at Filtering - Apache OpenOffice Wiki.

However, I found that the documentation did not describe all of the filter interfaces, for example TableFilterField3 for filtering multiple values. Use an introspection tool such as MRI or XrayTool to explore these options.

[newbie-01]

hi all,

got a solution in a german forum …

the file attached contains a macro that does the job,

the range of cells to be filtered must be defined as a ‘database range’? under <data - define range>,

it has some difficulties with LO ver. 5.4.5 and 5.4.6.2, the filtering of date strings formatted as YY-MM-DD failed as well as numeric values formatted with trumps (leading zeroes), the problem should / shall / may result from a new ‘engine’ in LO since ver. 5.2.x ???

LO version 5.1.6.2 does work better, no problems with trumps, 8 byte date strings need a little tuneup, i wrote a line for that and can work for all dates formatted as YY-MM-DD in the period from 2000-01-01 till 2099-12-31.

the line is:

if isdate(content) then content = "20"&content

and needs to be inserted in the macro.

any hints and enhancements appreciated …

thanks,

newbie-01

filter_dates_test.ods