Finding Insert Sheet event

The Application Programming Interface and the OASIS Open Document Format

Finding Insert Sheet event

Postby ken054 » Thu May 13, 2021 10:33 pm

G'day there One and All. I hope I've found the correct forum for my question, if not I'm sure someone can point me in the right direction.

By way of background, I've had a bit of experience with Excel VBA and prior to my retirement I was considered in the office to be the XL Guru. That in itself was a sad state of affairs reflecting on the office, rather than my abilities. Suffice to say that I'm untrained but a keen enthusiast.

I'm currently using LibreOffice 6.1.5.2 on Debian 10.

As to my question, I'm looking for a LibreOffice version of XL's "Sheet Add Event". I've written a small code routine that iterates through sheet names and builds a list of those whose names show the year - 2020, 2021, etc. These are then stored one per cell in a particular range on a worksheet. This routine works fine. However I need it to only run when a sheet is added or, less likely, deleted. Since it is more than likely that this will occur only once per year it is best triggered only when the number of sheets changes and so the "Sheet Insert" event would be the most appropriate.

I assume I'll need to build a listener, as per Andrew Pitonyak's excellent document. I further assume that I'll need to access the XSpreadsheetDocument interface, or the com.sun.star.sheet.SpreadsheetDocument service. I've modified Andrew's code to list the document events for Calc (I think correctly) but that doesn't reveal anything about sheets.

In short, I'm not sure where I'm likely to find the "Insert Sheet" event. It unfortunately doesn't appear in the "Tools | Customise | Events" tab and I'm quite lost as to how to proceed from here. If any one has any ideas, please tell me so I can get to work.

Thanks for reading,
Ken :)
LibreOffice 6.1.5.2 on Debian 10 with XFCE
ken054
 
Posts: 3
Joined: Thu May 06, 2021 9:17 am

Re: Finding Insert Sheet event

Postby Villeroy » Fri May 14, 2021 11:21 am

There seems to be no such event.
c.s.s.util.XChangesListener listens to inserted/removed rows and columns but not sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30174
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding Insert Sheet event

Postby ken054 » Fri May 14, 2021 12:42 pm

Thanks Villeroy. I wonder how they do it then. Obviously sheets can be added and deleted (duh) and I would have thought that such an action would be an event. If not, I wonder what the mechanism is. Weird.

Might be easier if I just use a Control Button to use a macro. I've got the "count the sheets" bit, just need to enter or delete a named sheet and then use what I've already got. Still seems strange though.

Thanks again, Villeroy. Much appreciated.
LibreOffice 6.1.5.2 on Debian 10 with XFCE
ken054
 
Posts: 3
Joined: Thu May 06, 2021 9:17 am

Re: Finding Insert Sheet event

Postby Villeroy » Fri May 14, 2021 1:09 pm

ken054 wrote:I wonder how they do it then.

Ask them.
IMHO, splitting equally structured data across separate sheets is a mistake.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30174
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding Insert Sheet event

Postby ken054 » Fri May 14, 2021 3:11 pm

Villeroy wrote:IMHO, splitting equally structured data across separate sheets is a mistake.


Quite possibly, but each sheet contains a simple list of indeterminate length. Separate sheets is just an easy way of labelling each list which will then be used for a summary table on a separate page. Quite possibly I'll build a pivot table out of that summary. I'll just see how it goes and if I have any difficulties I'll just amalgamate to a single sheet. If there's a loud explosion with flames & smoke I'll know it didn't work :lol:

Thanks again,
Ken
LibreOffice 6.1.5.2 on Debian 10 with XFCE
ken054
 
Posts: 3
Joined: Thu May 06, 2021 9:17 am

Re: Finding Insert Sheet event

Postby MrProgrammer » Fri May 14, 2021 6:10 pm

Hi, and welcome to the forum.

ken054 wrote:However I need it to only run when a sheet is added or, less likely, deleted.
If you're unsuccessful with finding a Sheet Added event perhaps you can use the Active Sheet Changed event instead. You can tell if a sheet has been deleted by comparing your list with the sheets enumeration. With the standard user interface, one deletes the active sheet, thus the Active Sheet Changed event will be called.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4114
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Finding Insert Sheet event

Postby JeJe » Sat May 15, 2021 1:21 am

Write a macro which does the sheet insert followed by your other stuff.
Replace the insert sheet item in the insert menu with an item that points to your macro.
Only use that button to insert a new sheet.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1652
Joined: Wed Mar 09, 2016 2:40 pm

Re: Finding Insert Sheet event

Postby eeigor » Sat May 15, 2021 6:36 am

You can catch sheet insert/remove events with ThisComponent.UndoManager (examples are on this forum), however @Villeroy is right that splitting equally structured data across separate sheets is a mistake. You need to add a field to differentiate your lists, this will make it easier to analyze data in the pivot table. The summary table isn’t needed.

UPD: To get the required list, you need to fill in the autofilter for the corresponding label.
The names of undo actions are localized (UndoManagerEvent.UndoActionTitle).
Ubuntu 18.04 LTS • LibreOffice 7.2.1.2 Community
eeigor
 
Posts: 184
Joined: Sun Apr 12, 2020 10:56 pm


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 4 guests