Bandwidth Monitor
Deleting an Excel WorkSheet from within a package
By Allan Mitchell

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
				bFound = True
				Exit For
			End if
		Msgbox "There is only one sheet.  Cannot delete it."
	End if

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

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

	Main = DTSTaskExecResult_Success
End Function