How to delete excel sheet from UFT

Question

I am trying to write a function which will delete all sheets except the one passed as parameter. Below function is being called but function does not delete any sheets. How can I delete all worksheets except one?

........

 Set ExcelObj = createobject("excel.application")
    ExcelObj.Visible = true
    Set ConfigFile = ExcelObj.Workbooks.Open (FilePath)
    Set ConfigSheet = ConfigFile.Worksheets("Scripts")
    Set ConfigApplicationSheet = ConfigFile.Worksheets("Applications")
    Set ExecutiveSummarySheet = ConfigFile.Worksheets("Summary")
    ExcelObj.ActiveWorkBook.SaveAs SummaryFilePath
    DeleteSheet "ConfigScripSheet","Summary"

    Function DeleteSheet(ConfigSheet,mySheetname)


        'Writing Name and Path of each File to Output File
        For Each ObjFile In ObjFiles
            ObjOutFile.WriteLine(ObjFile.Name & String(50 - Len(ObjFile.Name), " ") & ObjFile.Path)
        Next

        ObjOutFile.Close

        DeleteSheet = 0
        ExcelObj.DisplayAlerts = False
        For Each objWorksheet In ConfigSheet.Worksheets

            If not objWorksheet.Name = mySheetname Then
               DeleteSheet = 1
               ConfigScripSheet.sheets(objWorksheet.Name).Select
               ConfigScripSheet.sheets(objWorksheet.Name).Delete
               ExcelObj.DisplayAlerts = False

            End If

        Next

    End Function

Answer 1

Trying to correct your code above was too much of a minefield for me as I couldn't tell what you meant in several places - so I rewrote it based on what you had said in the description was your goal.

The code below will open the file, associate the objects the way you had them, pass the workbook object and a sheet name not to be deleted into the DeleteSheet function, which will delete any sheet in the workbook that is not named as per the passed in parameter SheetNameNotToDelete

Let me know if any of the code is unclear.

Option Explicit     ' Forces declaration of variables

Dim FilePath, SummaryFilePath   '<-- Need set to some value!
FilePath = ""
SummaryFilePath = ""
Dim ExcelObj : Set ExcelObj = CreateObject("Excel.Application")
Dim ConfigFile : Set ConfigFile = ExcelObj.Workbooks.Open(FilePath)
Dim ConfigSheet : Set ConfigSheet = ConfigFile.Worksheets("Scripts")
Dim ConfigApplicationSheet : Set ConfigApplicationSheet = ConfigFile.Worksheets("Applications")
Dim ExecutiveSummarySheet : Set ExecutiveSummarySheet = ConfigFile.Worksheets("Summary")

ExcelObj.ThisWorkbook.SaveAs SummaryFilePath

DeleteSheet ConfigFile, "Summary"

Function DeleteSheet(ByRef WorkbookObj, ByVal SheetNameNotToDelete)
    Dim oWorksheet
    For Each oWorksheet In WorkbookObj.Worksheets
        If oWorksheet.Name <> SheetNameNotToDelete And WorkbookObj.Worksheets.Count >=2 Then
            oWorksheet.Delete   ' Excel won't let you delete all worksheets from a workbook
        End If                  ' the check on Count >=2 covers the case where no worksheet exists
    Next                        ' called "Summary" to be left
End Function