[RESOLVED] Is the sheet exist???

[billhuard]

Hi Guys,

I'm looking for a macro or built-in fonction that can tell me, by the sheet name, if a sheet already exist in a workbook.

Excel 2003, VBA macro.

Thanks

[DKenny]

Function SheetExists(ByVal BookName As String, ByVal SheetName As String) As Boolean
Dim wksSheet As Worksheet
Dim bTemp As Boolean
 
    For Each wksSheet In Workbooks(BookName).Worksheets
        If wksSheet.Name = SheetName Then
              bTemp = True
              Exit For
        End If
    Next wksSheet
    
    SheetExists = bTemp
    
End Function

[billhuard]

I coudn't ask for better


thanks a lot

[DKenny]

I coudn't ask for better

Good, because I couldn't have done much better.

[DKenny]

Good, because I couldn't have done much better.

I lied...
You could also check to see of the workbook exists first...

Function SheetExists(ByVal BookName As String, ByVal SheetName As String) As Boolean
Dim wkbBook As Workbook
Dim wksSheet As Worksheet
Dim bTemp As Boolean
    
    For Each wkbBook In Application.Workbooks
        
        If wkbBook.Name = BookName Then
            
            For Each wksSheet In wkbBook.Worksheets
                
                If wksSheet.Name = SheetName Then
                      bTemp = True
                      Exit For
                End If
                
            Next wksSheet
            
        End If
        
        If bTemp Then Exit For
    Next wkbBook
    
    SheetExists = bTemp
    
End Function

[billhuard]

Thanks...

but just before I check if the sheet exists I'm opening the workbook.
So I would have trapped the error before