Page 1 of 1

[Solved]Delete multiple sheets (Alternative for removebyname

Posted: Mon Jul 30, 2018 5:09 am
by sokolowitzky
Hello,

I've been looking around for this for a while, but I could not find any answer.
How can I delete two sheets without writing their names on macro?

The only way to delete a sheet that I could find is;

Dim Doc As Object
Doc = ThisComponent
Doc.Sheets.removeByName("Sheet2")

but what I need is something like this; (I know there is no such a code, I just made it up to show what I'm looking for)
Dim Doc As Object
Doc = ThisComponent
Doc.getSheets.getByIndex(1,2).remove

Re: Delete multiple sheets (Alternative for removebyname)

Posted: Mon Jul 30, 2018 7:20 am
by FJCC
Here is a list of all of the methods for the Sheets container and I don't see any way to remove one except removeByName(). You can use getElementNames to get an array of sheet names and iterate over that. Why can't you use removeByName?

Code: Select all

(Name)
acquire                  
copyByName             
createEnumeration  
getByIndex  
getByName  
getCellByPosition  
getCellRangeByPosition
getCellRangesByName  
getCount  
getElementNames
getElementType   
getImplementationId 
getImplementationName 
getSupportedServiceNames
getTypes   
hasByName
hasElements
insertByName
insertNewByName
moveByName   
queryAdapter   
queryInterface 
release            
removeByName
replaceByName
supportsService

Re: Delete multiple sheets (Alternative for removebyname)

Posted: Mon Jul 30, 2018 8:07 am
by Villeroy
WRONG:

Code: Select all

shx = ThisComponent.getSheets()
for i = 1 to 3
  sh = shx.getByIndex(i)
  s = sh.getName()
  shx.removeByName(s)
next
After you removed i=1, the next one will be #2 which used to be #3 before the deletion.
After you removed i=2, the next one will be #3 which used to be #5 before the deletion.

BETTER:

Code: Select all

shx = ThisComponent.getSheets()
for i = 3 to 1 step -1
  sh = shx.getByIndex(i)
  s = sh.getName()
  shx.removeByName(s)
next
ALTERNATIVE:

Code: Select all

shx = ThisComponent.getSheets()
for each i in Array(9,7,5,3,1)
  sh = shx.getByIndex(i)
  s = sh.getName()
  shx.removeByName(s)
next

Re: Delete multiple sheets (Alternative for removebyname)

Posted: Mon Jul 30, 2018 10:48 am
by mcmurchy1917
This is how I delete all sheets except the first -

Code: Select all

		oSheets = oDoc.Sheets
		aSheetNames() = oSheets.getElementNames()
		for iSheet = 1 to ubound(aSheetNames)
		        oSheets.removeByName( aSheetNames(iSheet))
		next iSheet

Re: Delete multiple sheets (Alternative for removebyname)

Posted: Tue Jul 31, 2018 3:29 am
by sokolowitzky
Thanks to all. Each post has thought me a different thing.