check whether workbook contains a specific worksheet using VBA

Dear Experts:

below macro checks if a file exists in a specific folder.

I would like to expand the macro to accomodate the following requirement.

If the file is found, the macro is to further check whether a specific sheet by the name 'Overall_List' is present in the workbook.

If not, the macro is to say so and the macro is to abort.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas



Sub File_Exist_With_Dir()
    Application.ScreenUpdating = False
    Dim FilePath As String
    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
        MsgBox "File exist", vbInformation, "File exists"
        Call MyNewFile
    End If
    Application.ScreenUpdating = False
End Sub


Sub MyNewFile()

MsgBox ("hello")

End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Open in new window

Roy CoxGroup Finance ManagerCommented:
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

Open in new window

Rgonzo1971Commented:
Hi,

Or as a one liner
        SheetFound = Evaluate("=ISREF('" & "Overall_List" & "'!A1)")

Open in new window

Regards
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Andreas HermleTeam leaderAuthor Commented:
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
Andreas HermleTeam leaderAuthor Commented:
HI Rafael:

thank you very much for your swift help.

where am I to put this one-liner?
Fabrice LambertConsultingCommented:
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

Open 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

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Andreas,

Sorry! I didn't modify the filepath to be opened.

Please try...
Sub File_Exist_With_Dir()
    Dim FilePath As String
    Dim FileExists
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim SheetFound As Boolean
    Application.ScreenUpdating = False    
    FilePath = "C:\MyFolder\MyDocuments\Isele\ProductList.xlsx"
    On Error Resume Next
       FileExists = Dir(FilePath)
    On Error GoTo 0
    If FileExists = "" 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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
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

Open in new window

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

Thank you :-)
Andreas HermleTeam leaderAuthor Commented:
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.
Andreas HermleTeam leaderAuthor Commented:
HI Subodh, very nice coding. Thank you very much.

Regards, Andreas
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Andreas! Glad I could help. :)
Thanks for the feedback.
Roy CoxGroup Finance ManagerCommented:
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

Open in new window

Andreas HermleTeam leaderAuthor Commented:
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
Roy CoxGroup Finance ManagerCommented:
The UDF and the code associated with it should work, the rest of the code is yours.
Andreas HermleTeam leaderAuthor Commented:
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
Roy CoxGroup Finance ManagerCommented:
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

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.