Here is a simple and quick way of deleting a worksheet in an Excel spreadsheet. It uses two global variables, one for the Excel file name and one for the sheet to delete. As this uses OLE automation of the Excel application object, you will need Excel installed on any machine that this package executes on.

' 245 (DeleteExcelSheet)
Option Explicit

Function Main()

	Dim Excel_Application 
	Dim Excel_WorkBook 
	Dim Excel_WorkSheet
	Dim iSheetCounter
	Dim bFound

	Dim sFilename
	Dim sSheetName

	sFilename = DTSGlobalVariables("gv_ExcelFileLocation").Value
	sSheetName = DTSGlobalVariables("gv_SheetToDelete").Value
	
	Set Excel_Application = CreateObject("Excel.Application")

	' Open the workbook specified
	Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)

	bFound = False
	
	' Find out how many sheets are in the workbook as there must
	' be at least 1 visible sheet so if we are about to delete the
	' only valid sheet then abort


	iSheetCounter = Excel_WorkBook.WorkSheets.Count

	If iSheetCounter > 1 then
		' Now we need to make sure that the sheet to
		' be deleted  exists in the sheets available
		For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
			' Find the WorkSheet specified
			If Excel_WorkSheet.Name = CStr(sSheetName) Then
				Excel_WorkBook.Worksheets(sSheetName).Delete
				Excel_WorkBook.Save
				bFound = True
				Exit For
			End if
		Next
	Else
		Msgbox "There is only one sheet.  Cannot delete it."
	End if


	If bFound = True then
		Msgbox "Outcome = Sheet Deleted"
	Else
		MsgBox "Outcome = No Sheet Was deleted"
	End if

	'Clean Up our Excel Objects
	Set Excel_WorkSheet = Nothing
	Excel_WorkBook.Close
	Set Excel_WorkBook = Nothing
	Excel_Application.Quit
	Set Excel_Application = Nothing

	Main = DTSTaskExecResult_Success
End Function