Search folder for files that do not include a word

[Dave Patton]

Excel 2010
Windows 10 Home

How can I create a list of files in a folder that Do Not have a word such as zList?

 

[JLGWhiz]

The two macros will look in the directory for any file with file extension xlsm only. It will open each .xlsm file and check the last sheet name in that file. The first macro will return a list of files which Do have 'zList' as the last sheet name. The second macro will return a list of files which DO NOT have 'zList' as the last sheet name. Your choice.
To get file name with zList Sheet

Sub makeFileList()
 Dim fPath As String, fName As String, sh As Worksheet, wb As Workbook
 Set sh = ActiveSheet
 fPath = ThisWorkbook.Path
 If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
 fName = Dir(fPath & "*.xlsm")
     Do
         If fName = ThisWorkbook.Name Then GoTo SKIP:
             Set wb = Workbooks.Open(fPath & fName)
                On Error Resume Next
                    If wb.Sheets(wb.Sheets.Count).Name = "zList" Then
                        sh.Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
                        wb.Close False
                    End If
                On Error GoTo 0
SKIP:
             fName = Dir
     Loop While fName <> ""
 End Sub

To get file name without zList sheet.

Sub makeFileListNot()
 Dim fPath As String, fName As String, sh As Worksheet, wb As Workbook, ws As Worksheet, cnt As Long, flag As Boolean
 Set sh = ActiveSheet
 fPath = ThisWorkbook.Path
 If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
 fName = Dir(fPath & "*.xlsm")
     Do
         If fName = ThisWorkbook.Name Then GoTo SKIP:
             Set wb = Workbooks.Open(fPath & fName)
                On Error Resume Next
                    If wb.Sheets(wb.Sheets.Count).Name <> "zList" Then
                        sh.Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
                    End If
                On Error GoTo 0
                wb.Close False
SKIP:
             fName = Dir
     Loop While fName <> ""
 End Sub

 

[Dave Patton]

Sub makeFileListNot()

This sub completed but it did not list the 2 test files that do not have the zList sheet.
It listed 132 of 205 files that do have the zList sheet.

The Sub makeFileList did not complete.
It listed 71 files. The files were all left open.

Thanks for your help. We can leave this project. I can review the files manually.

 

[JLGWhiz]

I moved the close command on one of them and forgot to do it on the other. This is the revision.

Sub makeFileList()
 Dim fPath As String, fName As String, sh As Worksheet, wb As Workbook
 Set sh = ActiveSheet
 fPath = ThisWorkbook.Path
 If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
 fName = Dir(fPath & "*.xlsm")
     Do
         If fName = ThisWorkbook.Name Then GoTo SKIP:
             Set wb = Workbooks.Open(fPath & fName)
                On Error Resume Next
                    If wb.Sheets(wb.Sheets.Count).Name = "zList" Then
                        sh.Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name                        
                    End If
                On Error GoTo 0
                wb.Close False
SKIP:
             fName = Dir
     Loop While fName <> ""
 End Sub

don't understand why you are getting different results than me. My test files had 'zList' as the last sheet in the file and it found them and listed the file names. When I had files with No zList sheets the Not zList macro listed all of them. Note that it is only checking the last sheet, because in an earlier post you said that would be where the sheet resided. I can alter the macro to look at all the sheets if that is the problem.

 

[JLGWhiz]

This will be my final attempt at this. The macro below will check both conditions and list the file names which have zList on any sheet in the file in column A and those which have no zList in column B. If there is a problem with it, other than an error, I will not attempt to resolve it, because I have spent considerable time on this and it should have been a simple procedure to write.

Sub makeFileList()
 Dim fPath As String, fName As String, sh As Worksheet, wb As Workbook, ws As Worksheet, Flag As Boolean
 Set sh = ActiveSheet
 With sh
    .Range("A1") = "Has zList"
    .Range("B1") = "No zList"
 End With
 fPath = ThisWorkbook.Path
 If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
 fName = Dir(fPath & "*.xlsm")
     Do
         If fName = ThisWorkbook.Name Then GoTo SKIP:
             Set wb = Workbooks.Open(fPath & fName)
                On Error Resume Next
                Flag = False
                    For Each ws In wb.Sheets
                        If ws.Name = "zList" Then
                            sh.Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
                            Flag = True
                            Exit For
                        End If
                    Next
                    If Flag = False Then sh.Cells(Rows.Count, 2).End(xlUp)(2) = wb.Name
                    Flag = False
                On Error GoTo 0
                wb.Close False
SKIP:
             fName = Dir
     Loop While fName <> ""
 End Sub

 

[Dave Patton]

Your suggestions worked.

Thanks very much for your help.

 

[JLGWhiz]

Your suggestions worked.

Thanks very much for your help.

You're welcome,
regards, JLG