check whether workbook contains a specific worksheet using VBA

You may try something like this...

Sub File_Exist_With_Dir()
    Dim FilePath As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim SheetFound As Boolean
    Application.ScreenUpdating = False
    FilePath = ""
    On Error Resume Next
       FilePath = Dir("C:\MyFolder\MyDocuments\Isele\ProductList.xlsx")
    On Error GoTo 0
    If FilePath = "" Then
        MsgBox "File doesn't exist", vbInformation, "File not found"
        Exit Sub
    Else
        Set wb = Workbooks.Open(FilePath)
        On Error Resume Next
        Set ws = wb.Sheets("Overall_List")
        On Error GoTo 0
        If Not ws Is Nothing Then
            SheetFound = True
        End If
        wb.Close False
        If Not SheetFound Then
            MsgBox "File exists but the Sheet Overall_List doesn't exist in it.", vbExclamation
            Exit Sub
        Else
            MsgBox "File and Sheet both exist", vbInformation, "File exists"
            Call MyNewFile
        End If
    End If
    Application.ScreenUpdating = False
End Sub

Select allOpen in new window

I use a UDF to test if a sheet exists. Normally, I would place UDFs in a separate module

Option Explicit
Sub File_Exist_With_Dir()
    Dim oWb As Workbook
    Dim FilePath As String
    Const ShtNm As String = "Overall_List"

    FilePath = ""
    On Error Resume Next
    FilePath = Dir("C:\MyFolder\MyDocuments\Isele\ProductList.xlsx")
    On Error GoTo 0
    If FilePath = "" Then
        MsgBox "File doesn't exist", vbInformation, "File not found"
        Exit Sub
    Else
        On Error GoTo the_end
        Application.ScreenUpdating = False
        Set oWb = Workbooks.Open(FilePath)
        If WksExists(ShtNm) Then MyNewFile
the_end:
        Application.ScreenUpdating = False
    End Sub

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Select allOpen in new window

Hi,

Or as a one liner

        SheetFound = Evaluate("=ISREF('" & "Overall_List" & "'!A1)")

Select allOpen in new window

Regards

Hi Subodh,

thank you very much for your swift help.

I am afraid to tell you that your code throws an error message on line 15. The file  exists however: "C:\MyFolder\MyDocuments\Isele\ProductList.xlsx"

Roy, thank you very much for your swift and professional help.
I tested your code on the following basis: The file exists but the worksheet 'Overall_List' does not exist.

If I run your code nothing happens, actually the macro should tell me in a msgbox (Overall_List not found). Hope this is feasible. Thank you very much for your help in advance.

Regards, Andreas

HI Rafael:

thank you very much for your swift help.

where am I to put this one-liner?

Since the worksheets object is a collection, I would use a generic function that you'll be able to use somewhere else:

Dim app As Excel.Application
Set app createObject("Excel.Application")

    '// open your workbook
Dim wb As Excel.Workbook
Set wb = app.Workbooks.open "c:\ .........xlsx"

If(existInCollection("Overall_List", wb) Then
    '// worksheet exist, do Something here
Else
    '// worksheet does not exist, do Something else here.
End If

Select allOpen in new window

Public function existInCollection(byval key As String, byRef col As Collection) As Boolean
    existInCollection = existInCollectionByVal(key, col) or existInCollectionByRef(key, col)
End Function

    '// check if the item exist as a value
Private function existInCollectionByVal(byval key As String, byRef col As Collection) As Boolean
On Error goto Error
    Dim item As Variant
    item = col(key)
    existInCollectionByVal = True
Exit Function
Error:
    existInCollectionByVal = False
End Function

    '// Check if the item exist as a reference
Private function existInCollectionByRef(byval key As String, byRef col As Collection) As Boolean
On Error goto Error
    Dim item As Variant
    Set item = col(key)
    existInCollectionByVal = True
Exit Function
Error:
    existInCollectionByVal = False
End Function

Select allOpen in new window

then try

Sub FileAnd SheetExists()
    Dim oWb As Workbook
    Dim FilePath As String
    Const ShtNm As String = "Overall_List"

    FilePath = ""
    On Error Resume Next
    FilePath = Dir("C:\MyFolder\MyDocuments\Isele\ProductList.xlsx")
    On Error GoTo 0
    If FilePath = "" Then
        MsgBox "File doesn't exist", vbInformation, "File not found"
        Exit Sub
    Else
        Set oWb = Workbooks.Open(FilePath)
        If Evaluate("=ISREF('" & ShtNm  & "'!A1)") Then
             MyNewFile
        Else
             oWb .Close False
        Endif If
End Sub

Select allOpen in new window

Incredible, I am really happy to get so much help. Will try your solutions and then let you know.

Thank you :-)

Hi Rafael (Rgonzo). I am afraid to tell you that your code throws an 1004 error message on line 14.

Fabrice, I know that you are a very experienced and superb programmer. You code looks very sophisticated. But I am afraid to tell you that the your code gets highlighted red on line 'Set app createObject("Excel.Application")' as well as on 'Set wb = app.Workbooks.open "C:\MyDocuments\MyFiles\Aggregate_File.xlsx"'

Since Subodh's code works fine I will award all the points to him.

Again, thank you to all of you for your superb help.

HI Subodh, very nice coding. Thank you very much.

Regards, Andreas

You're welcome Andreas! Glad I could help. :)
Thanks for the feedback.

I missed you comment in my code.

If the worksheet exists it should call  your Procedure

Option Explicit
Sub File_Exist_With_Dir()
    Dim oWb As Workbook
    Dim FilePath As String
    Const ShtNm As String = "Overall_List"

    FilePath = ""
    On Error Resume Next
    FilePath = Dir("C:\MyFolder\MyDocuments\Isele\ProductList.xlsx")
    On Error GoTo 0
    If FilePath = "" Then
        MsgBox "File doesn't exist", vbInformation, "File not found"
        Exit Sub
    Else
        On Error GoTo the_end
        Application.ScreenUpdating = False
        Set oWb = Workbooks.Open(FilePath)
        If WksExists(ShtNm) Then 
                 MyNewFile '' 'calls your Procedure
       Else MsgBox ShtNm  & " does not exist in this workbook"
    End If
the_end:
        Application.ScreenUpdating = False
    End Sub

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Select allOpen in new window

Hi Roy,

thank you very much for following up on this. I am afraid to tell you the macro still does not produce the desired result.

Kind regards, andreas

The UDF and the code associated with it should work, the rest of the code is yours.

Hi Roy,

thank you very much for your feedback. I will re-test your solution and then get back to you.

Thank you very much for your great support.

Andreas

I've trimmed the code down so that you can see how the function works

Option Explicit


Sub File_Exist_With_Dir()

    Const ShtNm As String = "Overall_List"


    If WksExists(ShtNm) Then
        'MyNewFile '' 'calls your Procedure
        MsgBox "Sheet exists"
    Else: MsgBox ShtNm & " does not exist in this workbook"
    End If
the_end:
    Application.ScreenUpdating = False
End Sub

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Select allOpen in new window