How to Delete All The Sheets ( which have different "Sheet Names" ) Except "Sheet1" in Calc SpreedSheet, Macro Did not Work?

sv.thiyagarajan · October 3, 2023, 6:23am

Hi Friends,
Can you Help me Where I made the mistake?
I Wrote the Macro Which is “Deleting All The Sheets” Except “Sheet1”.
But, When I Run the Macro, it Deletes “Sheet1” also with other Sheets.

Note: Some Sheets have Named and Some Sheets have default Sheet Name

Here, is the Macro Code

==================
Private Sub Deleting_Sheets
	Dim Doc As Object, Sheets As Object, Sheet As Object
	Dim sheetName As String
	
	Doc = ThisComponent 
	Sheets = Doc.Sheets
	
	For Each Sheet In Doc.Sheets
	    sheetName = Sheet.Name
	  	  IF sheetName <> Sheets.hasByName("Sheet1")	Then
  			Sheets.removeByName(sheetName)	
  		  End IF
	Next
End Sub

And Here, Is the Attached File and Screen Shot

Deleting_Sheets_macros.ods (9.1 KB)

Please help me to Correct Code …

karolus · October 3, 2023, 6:48am

returns True or False … I don’t think you meant to compare it with the sheetname.

as bonus, working python-code:

def remove_except( sheetname="Sheet1" ):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    for name in sheets.ElementNames:
        if name != sheetname:
            sheets.removeByName(name)

sv.thiyagarajan · October 3, 2023, 7:56am

How to Write in StarBASIC Code …In your Python Code

def remove_except( sheetname=“Sheet1” ):
doc = XSCRIPTCONTEXT.getDocument()
sheets = doc.Sheets
for name in sheets.ElementNames:
if name != sheetname:
sheets.removeByName(name)

===================================================================
Private Sub Deleting_Sheets
Dim Doc As Object, Sheets As Object, Sheet As Object
Dim sheetName As String

	Doc = ThisComponent 
	Sheets = Doc.Sheets
	sheetName = "Sheet1"
	
	For Each Sheet In Sheets.ElementNames
		  	IF Sheet <> sheetName	Then
		  		    Sheets.removeByName(Sheet)	
		  	End IF
	Next

End Sub

Is it Correct …

JohnSUN · October 3, 2023, 7:19am

So there are two errors in your code - one is obvious, which @karolus pointed out in his answer: sheetName should be compared with the string “Sheet1”.
And the second mistake is not so obvious. The For Each loop works well if you are going through all the items in a collection. But if you DELETE elements of a collection in a loop, then don’t expect a good result: when you delete an element, the collection inside the body of the loop will no longer contain the same set of elements that was in the loop header, the result is unpredictable. Therefore, for the removal operation, you should use a loop from the largest element to the smallest:

Private Sub Deleting_Sheets
	Dim Doc As Object, Sheets As Object, Sheet As Object
	Dim sheetName As String
	Dim i As Long 
	Doc = ThisComponent 
	Sheets = Doc.getSheets()
	
	For i = Sheets.getCount()-1 To 0 Step -1
		Sheet = Sheets.getByIndex(i)
	    sheetName = Sheet.Name
	  	  IF sheetName <> "Sheet1"	Then
  			Sheets.removeByName(sheetName)	
  		  End IF
	Next
End Sub

karolus · October 3, 2023, 7:23am

therefore we should loop through sheets.ElementNames instead doing silly indexing

JohnSUN · October 3, 2023, 7:31am

Yes, you’re right, in this case we have such an opportunity. The “backwards” loop is a universal approach, it will work for deleting rows, or for clearing DrawPage of inserted images, or for other cases when we cannot get a list of object names for processing - this code is only an example of a solution.

sv.thiyagarajan · October 3, 2023, 8:04am

How to Use Sheets.ElementNames… I got Error message While Using in StarBASIC Code

Private Sub Deleting_Sheets
Dim Doc As Object, Sheets As Object, Sheet As Object
Dim sheetName As String

Doc = ThisComponent 
Sheets = Doc.Sheets
sheetName = "Sheet1"

For Each Sheet In Sheets.ElementNames
	  	IF Sheet <> sheetName	Then
	  		    Sheets.removeByName(Sheet)	
	  	End IF
Next

End Sub

sv.thiyagarajan · October 3, 2023, 8:11am

But How to Write Code Using Sheets.ElementNames

Here, I am getting Error Message

Private Sub Deleting_Sheets
		Dim Doc As Object, Sheets As Object, Sheet As Object
		Dim sheetName As String
		
		Doc = ThisComponent 
		Sheets = Doc.Sheets
		sheetName = "Sheet1"
		
		For Each Sheet In Sheets.ElementNames
			  IF Sheet <> sheetName	Then
			  	   Sheets.removeByName(Sheet)	
			  End IF
		Next 
End Sub

JohnSUN · October 3, 2023, 8:22am

Please, when publishing code in the future, use this editor feature:

Actually in your script it is not an Object, but a String - try , Sheet As String

sv.thiyagarajan · October 3, 2023, 8:34am

Thanks JohnSUN… 3 Thinks… Your right …to correct my Doubts…

For Long Days i have One Doubt , When ever I posting Code either its Small or Big… My Code Won’t Show Proper Way… Now, I Understand what i made mistake … and As Per Your Screen Shots I Follow up … Thanks…for that because, Without Asking this Doubt … Your Clearing that one…
Deleting Sheets , Your Code ( For = i Sheet.getCout()-1 To 0 Step -1 ) Working… thanks…
for Clearing my Doubts.
Deleting Sheets, Using @karolus Python Code into STAR BASIC Code… You Clearing My Doubts… and I Correct my Code …yes, Its Work…Thanks…

I will Post The Two Codes… and Close this Topic As Solution

Thanks To @JohnSun & @karolus …All the Credits goes to them… regarding this Topic Solution

Here is the Solution Code

Using For … Next Loop

Private Sub Deleting_Sheets
	Dim Doc As Object, Sheets As Object, Sheet As Object
	Dim sheetName As String
	Dim i As Long 
	
	Doc = ThisComponent 
	Sheets = Doc.getSheets()
	
	For i = Sheets.getCount()-1 To 0 Step -1
		Sheet = Sheets.getByIndex(i)
	    sheetName = Sheet.Name
	  	  IF sheetName <> "Sheet1"	Then
  			Sheets.removeByName(sheetName)	
  		  End IF
	Next
End Sub

Using For Each… Loop Using Sheet.ElementNames
===========================================

Private Sub Deleting_Sheets
		Dim Doc As Object, Sheets As Object, Sheet As String
		Dim sheetName As String
		
		Doc = ThisComponent 
		Sheets = Doc.Sheets
		sheetName = "Sheet1"
		
		For Each Sheet In Sheets.ElementNames
			  IF Sheet <> sheetName Then
			  	Sheets.removeByName(Sheet)	
			  End IF
		Next 
End Sub

Here, is the macro containing sheet with code

Deleting_Sheets_macros.ods (11.8 KB)

mikekaganski · October 3, 2023, 8:55am

@sv.thiyagarajan: Just one small bit:
make sure that your code is prepared for a case where there’s no “Sheet1” sheet - in which case, there will be an attempt to remove the last sheet in the file, and that will error out.

sv.thiyagarajan · October 3, 2023, 9:05am

Thanks, @mikekaganski , Yes, Just Now i tried as per your above comment… yes, It showing error, where there is no “Sheet1” not in that Calc Spreadsheet

JohnSUN · October 3, 2023, 9:35am

… and just for this you can use the same Sheets.hasByName("Sheet1") from your original code

karolus · October 3, 2023, 10:46am

a byte of code_golf:

def code_golf(sn="Sheet1"):
    sh=XSCRIPTCONTEXT.getDocument().Sheets
    if sh.hasByName(sn):
        [sh.removeByName(name) for name in sh.ElementNames if name!=sn]