How to access addNewByScopeName()

[studog]

addNewByScopeName

My LibreOffice Calc v6.0.7.3 can do this via the UI, so it should be possible.

I’m porting my investment tracking spreadsheet from Excel to Calc and it depends on having scoped named ranges. I can do this:

' Named ranges
Dim named as Object
Dim baseAddr as new com.sun.star.table.CellAddress

named = ThisComponent.NamedRanges

baseAddr.sheet = 0  ' TODO: Make this new_sheet's index
baseAddr.column = 10
baseAddr.row = 0
named.addNewByName("Cur_Price", "$K$1", baseAddr, 0)

What I need though is to use AddNewByScopeName. Changing the function call name throws a method not found error. Googling turns up nothing useful.

[mikekaganski]

This interface is not existing in LibreOffice. It was added to AOO in bz#120478, and that was not ported to LO.

Possibly @erAck has some idea how to access sheet-local names from UNO API in LO, and if it makes sense in LO to implement support for that new AOO UNO interface for interoperability.

[studog]

Thanks for the help. I had dismissed the macro recorder as having no value because it records dispatch commands instead of API commands; I’ll give it a try here to see what I can learn.

[studog]

Using
(Menubar) Sheet → (Dropdown) Named Ranges and Expressions → (Dropdown) Manage…
I added a scoped named range while recording a macro. I did it twice to make sure I hadn’t screwed up. Here’s the output:

sub _sheetScopeNamedRange
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:DefineName", "", 0, Array())


end sub

That’s… not helpful.

[erAck]

if it makes sense in LO to implement support for that new AOO UNO interface for interoperability

That API is clumsy and awkward in comparison. You need to know and pass the “ScopeName” (sheet name) with each and every call.

[erAck]

You can obtain a sheet-local XNamedRanges interface with any of

named = ThisComponent.Sheets(0).NamedRanges
named = ThisComponent.Sheets("Sheet1").NamedRanges
named = ThisComponent.Sheets.getByIndex(0).NamedRanges
named = ThisComponent.Sheets.getByName("Sheet1").NamedRanges

and on that use named.AddNewByName(...).

[studog]

XY problem strikes again! What I actually need is to be able to define scoped names ranges, and this is the way to do it.

I agree, this is better than addNewByScopeName().

Thanks!

(I don’t have enough points to upvote.)

[sokol92]

By the way, the documentation does not indicate that the Spreadsheet service has the attribute NamedRanges.

[mikekaganski]

@sokol92: seems that Spreadsheet.idl misses

[readonly, property] com::sun::star::sheet::XNamedRanges NamedRanges;

Do you want to provide a patch? I could mentor if you want.

[erAck]

Indeed, it’s missing. Should be [optional, readonly, property] though, as versions earlier than (which? would have to dig through commit history for the @since tag) don’t have it.

[sokol92]

Thank you, Mike, I am sure you will do it much more reliably. I am already confident in reporting bugs

[sokol92]

Looked in my notes - the (often used) getEvents method is also not described in the Spreadsheet documentation. Maybe fix it at the same time?

[erAck]

It is: LibreOffice: XEventsSupplier Interface Reference

See inheritance diagram, interface css::document::XEventsSupplier is exported by the css::document::OfficeDocument service, which is included in the css::sheet::SpreadsheetDocument service.

[sokol92]

Thanks, I’m sorry, but I wrote about the same Spreadsheet service.
The following statement is executed in Calc without error:

oEvents=ThisComponent.Sheets(0).getEvents

[erAck]

Hum yeah, there should be an optional interface document::XEventsSupplier exported by the sheet::Spreadsheet service (I guess since OOo3.3).

[sokol92]

Thank you very much, looking forward to legalizing invisible members.