Highlighted
Respected Contributor.
Respected Contributor.
377 views

How to delete excel worksheet

Hello,

I would like to delete excel worksheets excep one. When I run my code, i get warning. How can I delete the worksheets?

Set ExcelObj = createobject("excel.application")
ExcelObj.Visible = true
wait 5
Set ExcelTestScript = ExcelObj.Workbooks.Open ("C:\Users\aa.xlsx")
wait 5
Set SheetOne = ExcelTestScript.Worksheets("Sheet1")
DeleteSheet ExcelTestScript,SheetOne
Function DeleteSheet(ExcelTestScript,mySheet)
Application.DisplayAlerts = False
For Each objWorksheet In ExcelTestScript.Worksheets

	If not objWorksheet.Name = mySheet Then
		objWorksheet.delete
		Set WshShell = CreateObject("WScript.Shell")
		WshShell.sendkeys "{ENTER}" 'Select the option
		Application.DisplayAlerts = True
	End If

Next
	
End Function

Thanks.

0 Likes
2 Replies
Highlighted
Valued Contributor.
Valued Contributor.

Re: How to delete excel worksheet

delete-a-worksheet-quicker-using-keyboard-shortcuts

You can delete worksheets using shortcuts just type shortcut by .Type and check text on pop-up, repeat "F4" as "delete" exist. 

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

Re: How to delete excel worksheet

You can also supress warnings at the beginning of your code and enable them at the end:

 

Application.DisplayAlerts = False

 ' Do delete worksheet process

Application.DisplayAlerts = True

Mark Smith

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.