Delete or rename an Excel file if it does not contain a sheet with a specific name

[Ovisele]

Hi Guys,

Please indulge me in the following problem. I need to parse over 700 files in order to establish if they contain or not a specific sheet "Corrective Saccades". If this particular sheet is present in the file, I would like to rename that file, or rename/delete the files that are not containing that specific sheet - whatever is easier.

An example file is here: Corrective Saccades.xlsx

Please see my piece of code below. Many thanks in advance!

Sub LoopFiles()

    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here
   For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name = "Corrective Saccades" Then
           [B] WHAT DO I DO NEXT?[/B]
        End If
    Next
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
            End With
            xFileName = Dir
        Loop
    End If
End Sub

 

[Ovisele]

If you want to delete the files try

Sub LoopFiles()

   Dim xFd As Object
   Dim xFdItem As Variant
   Dim xFileName As String
 
   Set xFd = Application.FileDialog(4)
   If xFd.Show = -1 Then
      xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
   End If
   xFileName = Dir(xFdItem & "*.xls*")
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   Do While xFileName <> ""
      With Workbooks.Open(xFdItem & xFileName)
         If ShtExists("Corrective Saccades", Workbooks(.Name)) Then
            .Close False
         Else
            .Close False
            Kill xFdItem & xFileName
         End If
         Application.DisplayAlerts = False
      End With
      xFileName = Dir
   Loop
End Sub
Public Function ShtExists(shtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(Wbk.Sheets(shtName).Name) = LCase(shtName))
    On Error GoTo 0
End Function

This also works! Thank you guys, you are brilliant. Unfortunately, I cannot mark both answers as solutions but Kudos to you both!

 

[Fluff]

Glad to help & thanks for the feedback.