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.