mercredi 28 juin 2017

Avoid adding duplicate sheets to Excel Work book

I have a collection words inside SheetNames and i'm trying to add new Worksheet for each word inside SheetNames, please find the code below.

Before adding the Worksheet i'm trying to validate whether the sheet is already existing in my workbook using sheetExists function, code provided below.

For Each SheetName In SheetNames

     If sheetExists(SheetName , newWB) = False Then
        newWB.Activate
        Set FilPage = Worksheets.Add
        FilPage.Activate
        SheetName = Replace(Replace(Replace(Replace(Replace(SheetName, ".", " "), "[", " "), "]", " "), "/", "_"), "\", " ")
        If Len(SheetName) <= 30 Then
            FilPage.Name = SheetName
        Else
            SheetName = Left(SheetName, 23) & "-trimed"
        End If
        ActiveSheet.Range("A1").Activate
        ActiveCell.PasteSpecial
    End If
Next

The code valediction using function sheetExists is not working consistently.

Function sheetExists(sheetToFind ,wb As Excel.Workbook) As Boolean

    WS_Count = ActiveWorkbook.Worksheets.Count

    sheetExists = False

    For I = 1 To WS_Count
        If ActiveWorkbook.Worksheets(I).Name = sheetToFind Then
            sheetExists = True
            Exit Function
        End If
    Next

End Function

i can see some of the worksheet added with names "Sheet99" even though SheetName is passed and sometimes if function sheetExists function returns True still workbook is trying to add a worksheet

Aucun commentaire:

Enregistrer un commentaire