how to check if a sheet exist?

[jamiguel77]

hi, i know how to:

Dim WorksheetExists As Boolean
WorksheetExists = Evaluate("ISREF('Graficas'!A1)")
and work

But i am work with some workbooks....
and then i need evaluate if exist sheets on each workbook.

this:

WorksheetExists = Evaluate("ISREF('Graficas'!A1)")

Evaluate in the last opened Xls File...


here more code:


Dim wbk As Workbook
Dim wbk2 As Workbook

Set wbk2 = Workbooks.Open(Filename:=Filename2)
Set wbk = Workbooks.Open(Filename:=Filename1)


how to check if sheet named: 'Graficas' exist on wbk2 and wbk?


Thanks

 

[Trebor76]

Hi jamiguel77,

Here's a custom function (UDF) that you can run to check if a sheet tab exists within an open workbook of the same Excel session the code is being run in:

Sub Macro3()

    Dim wbk As Workbook
    Dim wbk2 As Workbook
    
    Set wbk = Workbooks.Open(Filename:=Filename1)
    Set wbk2 = Workbooks.Open(Filename:=Filename2)
    
    If WorkSheetExists(wbk.Name, "Graficas") = False Then 'Just change the workbook and sheet names to run the function each time
        MsgBox "Sheet tab doesn't exist"
    Else
        MsgBox "Sheet tab does exist"
    End If
    
End Sub
'User Defined Function (UDF) to determine if a particular sheet
'exists in a specified workbook.
'Note the specified workbook must be open in the same Excel session the code is being run in.
Function WorkSheetExists(strWBName As String, strWSName As String) As Boolean

    Dim wbk As Workbook
    Dim ws  As Worksheet

    If Len(Trim(strWBName)) = 0 Or Len(Trim(strWSName)) = 0 Then
        WorkSheetExists = Empty
        Exit Function
    End If
    
    'If the 'strWBName' doesn't exist (open) in the current Excel session, then...
    On Error Resume Next
        Set wbk = Workbooks(strWBName)
        If Err.Number <> 0 Then
            '...set the 'WorkSheetExists' and quit
            WorkSheetExists = Empty
            Exit Function
        End If
    On Error GoTo 0
    
    On Error Resume Next
        Set ws = wbk.Sheets(strWSName)
        If Err.Number <> 0 Then
            WorkSheetExists = False
        Else
            WorkSheetExists = True
        End If
    On Error GoTo 0

End Function

Regards,

Robert

 

[rlv01]

Function WorkSheetExists(WB As Workbook, SheetName As String) As Boolean
    Dim WS As Worksheet, Found As Boolean

    For Each WS In WB.Worksheets
        Found = (WS.Name = SheetName)
        If Found Then Exit For
    Next WS

    WorkSheetExists = Found
End Function