Ask Your Question
0

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

asked 2018-04-01 23:30:59 +0200

newbie-01 gravatar image

updated 2018-04-07 20:22:36 +0200

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

edit retag flag offensive close merge delete

Comments

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.

Jim K gravatar imageJim K ( 2018-04-02 16:07:31 +0200 )edit

Most importantly, please follow up on your other question at https://ask.libreoffice.org/en/questi.... 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.

Jim K gravatar imageJim K ( 2018-04-02 16:09:41 +0200 )edit

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.

Jim K gravatar imageJim K ( 2018-04-02 16:14:29 +0200 )edit

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 gravatar imagenewbie-01 ( 2018-04-02 17:12:50 +0200 )edit

@Jim K 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 gravatar imagenewbie-01 ( 2018-04-02 17:18:07 +0200 )edit

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,
newbie-01 gravatar imagenewbie-01 ( 2018-04-02 17:19:14 +0200 )edit

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

Jim K gravatar imageJim K ( 2018-04-04 22:20:29 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-04-04 23:28:40 +0200

Jim K gravatar image

updated 2018-04-04 23:34:48 +0200

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 https://wiki.openoffice.org/wiki/Docu....

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.

edit flag offensive delete link more
0

answered 2018-04-07 20:22:04 +0200

newbie-01 gravatar image

updated 2018-04-07 20:34:02 +0200

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-04-01 23:30:59 +0200

Seen: 327 times

Last updated: Apr 07 '18