How to check sheet Name exist or not in particuler workbook

[mohan.r1980]

Hi Experts,

i have multiple folders and have multiple excel files in it.
i am trying to open each folder and open each excel file
when i open excel file of that particular folder i have extract some date from the sheet Name ("Enter Data").

Problem is that some of the excel have no such type of sheets so i just want to know how do i check the sheet name "Enter Data") is available or not

Please suggest.

My Code is below

 For Each m In mFol.SubFolders
      sFileName = Dir(m.Path & "\*.xls*")
      
              Do While Len(sFileName) > 0
                    
                  Workbooks.Open (m.Path & "\" & sFileName)
                  
                        Sheets("ENTER DATA").Select
                    
                  ActiveWorkbook.Close False
              
              
              sFileName = Dir
              Loop
  Next m

[mikerickson]

You could use this UDF to find if a given workbook, has a sheet with a particular name.

Function SheetExists(SheetName as String, optional wb as Workbook) As Boolean
    If wb Is Nothing then Set wb=ActiveSheet

    On Error Resume Next
    SheetExists =(LCase(wb.Sheets(sheetName).Name) = LCase(SheetName))
    On Error Goto O
Exit Function

[daboho]

Hi Experts,

i have multiple folders and have multiple excel files in it.
i am trying to open each folder and open each excel file
when i open excel file of that particular folder i have extract some date from the sheet Name ("Enter Data").

Problem is that some of the excel have no such type of sheets so i just want to know how do i check the sheet name "Enter Data") is available or not

Please suggest.

My Code is below

 For Each m In mFol.SubFolders
      sFileName = Dir(m.Path & "\*.xls*")
      
              Do While Len(sFileName) > 0
                    
                  Workbooks.Open (m.Path & "\" & sFileName)
                  
                        Sheets("ENTER DATA").Select
                    
                  ActiveWorkbook.Close False
              
              
              sFileName = Dir
              Loop
  Next m

Not test maybe like this

sub test()
Dim myfile as object,f as object
Dim s as string 
Dim fso as object,arr(),j as long
Set fso = CreateObject("Scripting.FileSystemObject")
s ="Enter Data"
Set f =fso.Getfolder("your path")
For each myfile in f.subfolders.files
   Dim wb as workbook,ws as worksheet
   Set wb = Workbooks.open(myfile)
     With wb
       If not Evaluate("isreff('" & s & "'!A1")") then
          j = j + 1
          Redim Preserve arr(1 to j)
          arr(j) = wb.name
       End if
      .close ,false
     End with
Next myfile
    
Dim v
  For each v in arr
   Msgbox "This file " & v & " Not have sheet Enter Data",64
  Next v
End sub

[CK76]

I'd do it like mikerickson, but using IsObject function instead of using text comparison.

Ex:

Function ExistSheet(Ws As String, Optional Wb As Workbook) As Boolean
    If Wb Is Nothing Then Set Wb = ActiveWorkbook
    On Error Resume Next
    ExistSheet = IsObject(Wb.Sheets(Ws))
End Function

[mohan.r1980]

thanks for reply

mikerickson and CK76 your solution working fine.

thanks you very much to all.