[Solved] Find Insert Sheet event

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ken054
Posts: 3
Joined: Thu May 06, 2021 9:17 am

[Solved] Find Insert Sheet event

Post by ken054 »

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 :)
Last edited by MrProgrammer on Wed Sep 21, 2022 9:20 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
LibreOffice 6.1.5.2 on Debian 10 with XFCE
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding Insert Sheet event

Post by Villeroy »

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
ken054
Posts: 3
Joined: Thu May 06, 2021 9:17 am

Re: Finding Insert Sheet event

Post by ken054 »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding Insert Sheet event

Post by Villeroy »

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
ken054
Posts: 3
Joined: Thu May 06, 2021 9:17 am

Re: Finding Insert Sheet event

Post by ken054 »

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
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Finding Insert Sheet event

Post by MrProgrammer »

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, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Finding Insert Sheet event

Post by JeJe »

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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Finding Insert Sheet event

Post by eeigor »

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.5.3.2 Community
Post Reply