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