calc access "__Anonymous_Sheet_DB__0" from macro

[newbie-02]

hello and tia for any help,

while working with calc and ‘autofilter’ one can either define a ‘database-range’ and apply filters to that, or, if having a ‘top row’ with non empty cells, choose data - autofilter and calc will automatically create a - temporary? - range covering all connected cells up to full-empty columns left and right and full-empty rows below and above that range. right? for that range one can set autofilter or even standard and advanced filters, they are saved with the file and active on reload.

(it’s only possible to have one such range at a time, when applying autofilter to another range not defined as a database range the first ‘virtual’ one disappears. right?)

i’d like to know if it’s possible to ‘detect’ and use / manipulate this range and it’s criteria from a macro in a similar manner as one can do with ‘~databaseranges.getbyname(xx)’ and ‘~.getfilterfields’ and so on.

i’ve seen a ‘__Anonymous_Sheet_DB__0’ range defined in the ods file, but
… Databaseranges.getByName(__Anonymous_Sheet_DB__0) produced:
“BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException”
while Databaseranges.getByName("__Anonymous_Sheet_DB__0")
is evaluated but triggers ‘on error goto’, thus evaluates to error.

anybody here who knows a way to access that structure?

pls. don’t tell me ‘just define a range’ … i know i can do that, my target is to have macros which work on any filtered range even on files from other people where i don’t know about strutures and definitions, and always having to look manually ‘is this a defined range, or a “virtual”/temporary one?’ is … not very comfortable …

reg.

b.

[erAck]

(it’s only possible to have one such range at a time, when applying autofilter to another range not defined as a database range the first ‘virtual’ one disappears. right?)

Almost, it’s one per sheet.

These anonymous ranges can not be enumerated or obtained by name, but there is the css::sheet::XUnnamedDatabaseRanges interface at which if hasByTable(nTab) returns true the getByTable(nTab) call returns the associated css::sheet::XDatabaseRange in its Any.

Note that XUnnamedDatabaseRanges is not published stable so its declaration and/or working may change without further notice, but it’s the same since LibreOffice 3.5

[newbie-02]

hello @Eike,

thanks for clarification , i’m not used to this structure, will try to learn in the future,

as you have profound knowledge may i ask for help with that bug:

filesave: fileopen: macro: xml: calc filtered ranges wrongly saved in .ods format?

it’s the same area: filters in calc, I think I have identified a clear error there, also narrowed it down, but a correction with consideration of all functionalities and dependencies overtaxes me and my time …

reg.

b.

[wrighch]

@newbie-02, I’m trying to do exactly the same thing as you – use a macro to manipulate an AutoFilter which has no explicitly-defined Database Range. The explanation provided by @erAck is unfamiliar to me too and, though I dislike the idea of using unpublished APIs, this is intriguing enough to pursue, if only for a better understanding of filters. Have you pursued his suggestion and, if so, can you post some example code? How do you use the XUnnamedDatabaseRanges Interface?

[newbie-02]

hello @wrigch,

no, didn’t have time yet and ‘can work’ whith what i have and with defined database ranges, i don’t add rows at top or bottom, thus the defined range always covers all neccessary data on the sheet,

i avoid stuff as ‘XUnnamedDatabaseRanges’ if possible, it’s things ‘changing in the background’, difficult access to … ‘unstable’ … not what i like …

code / sample … i’d build us four functionalities ‘filter act. col acc. act. cell value’, ‘unfilter act. col’ (in fact it’s one macro, if unfiltered → filter, if filtered → unfilter) and ‘step forward’ / ‘step backward’ through the valid values for that colum, quite long, somewhat ‘wooden’, but works and is - customized on keyboard shortcuts - very handy for for fast flexible selections, leave a note if you can use …

[wrighch]

Thanks for the reply @newbie-02. As you might note from my recent post here I accepted an answer that allowed me to do what I wanted to do. And I agree with you that using unpublished/unsupported APIs is risky. But I’d still love to see some LibreOffice Basic code that instantiates the XUnnamedDatabaseRanges interface that @erAck references. I’m surprised (not surprised!) that no one seems to know how to do that. Or, at least, that no one has provided an example of how to do it. Connecting to an ‘Interface’ is still a step too far down the abstraction path for me.

[newbie-02]

think @erAck will know, and there are some macro-experts around here … have luck and one will write …