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)
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
Msgbox "There is only one sheet. Cannot delete it."
If bFound = True then
Msgbox "Outcome = Sheet Deleted"
MsgBox "Outcome = No Sheet Was deleted"
'Clean Up our Excel Objects
Set Excel_WorkSheet = Nothing
Set Excel_WorkBook = Nothing
Set Excel_Application = Nothing
Main = DTSTaskExecResult_Success