[Solved] Macro to delete sheet

Discuss the spreadsheet application

[Solved] Macro to delete sheet

Postby Nocton » Sun Jul 08, 2018 3:48 pm

I have a macro to delete a sheet ('Working') no longer needed:
Code: Select all   Expand viewCollapse view
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "aTableName"
args3(0).Value = "Working"
dispatcher.executeDispatch(document, ".uno:Show", "", 0, args3())
dispatcher.executeDispatch(document, ".uno:Remove", "", 0, Array())

It all works OK. But when I run it I always get the message: "Are you sure you want to delete the selected sheets?". How can I avoid seeing this message?
Last edited by Nocton on Sun Jul 08, 2018 4:51 pm, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 505
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Macro to delete sheet

Postby FJCC » Sun Jul 08, 2018 4:22 pm

Use the API(Application Programming Interface) instead of recorded commands.
Code: Select all   Expand viewCollapse view
oSheets = ThisComponent.getSheets()
oSheets.removeByName("Working")
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7262
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to delete sheet

Postby Villeroy » Sun Jul 08, 2018 4:33 pm

The MRI extension can record valid API code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Macro to delete sheet

Postby Nocton » Sun Jul 08, 2018 5:07 pm

How can I do that, Villeroy, as I see nothing about recording on the Macros menu.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 505
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Macro to delete sheet

Postby RoryOF » Sun Jul 08, 2018 5:09 pm

Complete MRI documentation is at
https://github.com/hanya/MRI/wiki
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29473
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Macro to delete sheet

Postby Villeroy » Sun Jul 08, 2018 6:16 pm

To be more precise, it does not record your actions in the office UI. It records your double-clicks in the MRI window.
Starting from any spreadsheet window with more than one sheet
menu:Tools>AddOns>MRI (inspects the current document)
Make the MRI window as tall as possible
Hit Ctrl+H to open the code sub-window
Double-click pseudo-property "Sheets" or method "getSheets"
Double-click method "getByName" and select a name
Click the < button to go back to the sheets collection
Double-click method removeByName and enter the sheet name
The sheet is removed and you have all the code in the code sub-window.
menu:Tools>Code>[some language] and you see the same code in another language
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Macro to delete sheet

Postby Nocton » Sun Jul 08, 2018 8:27 pm

Many thanks, Villeroy, that is really helpful.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 505
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests