Ask Your Question

How to access addNewByScopeName()

asked 2021-04-27 01:37:18 +0200

studog gravatar image

updated 2021-04-27 01:42:15 +0200


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

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.

edit retag flag offensive close merge delete


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.

Mike Kaganski gravatar imageMike Kaganski ( 2021-04-27 08:27:51 +0200 )edit

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 gravatar imagestudog ( 2021-04-27 14:50:20 +0200 )edit

(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("")

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

end sub

That's... not helpful.

studog gravatar imagestudog ( 2021-04-27 14:58:45 +0200 )edit

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 gravatar imageerAck ( 2021-04-27 22:00:12 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-04-27 21:44:00 +0200

erAck gravatar image

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(...).

edit flag offensive delete link more


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().


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

studog gravatar imagestudog ( 2021-04-27 22:37:23 +0200 )edit

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

sokol92 gravatar imagesokol92 ( 2021-04-28 13:10:55 +0200 )edit

@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.

Mike Kaganski gravatar imageMike Kaganski ( 2021-04-28 16:24:06 +0200 )edit

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.

erAck gravatar imageerAck ( 2021-04-28 16:42:16 +0200 )edit

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

sokol92 gravatar imagesokol92 ( 2021-04-28 16:47:18 +0200 )edit

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

sokol92 gravatar imagesokol92 ( 2021-04-28 17:38:55 +0200 )edit

It is:

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

erAck gravatar imageerAck ( 2021-04-29 19:30:54 +0200 )edit

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

sokol92 gravatar imagesokol92 ( 2021-04-29 19:58:36 +0200 )edit

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

erAck gravatar imageerAck ( 2021-04-30 18:06:20 +0200 )edit

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

sokol92 gravatar imagesokol92 ( 2021-04-30 18:15:30 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2021-04-27 01:37:18 +0200

Seen: 20 times

Last updated: Apr 27