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]

Assume your host workbook is in the same directory as the files you want to screen.

Sub makeFileList()
Dim fPath As String, fName As String, sh As Worksheet
Set sh = ActiveSheet
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If InStr(fName, "ZList") = 0 Then
            sh.Cells(Rows.Count, 1).End(xlUp)(2) = fName
        End If
        fName = Dir
    Loop
End Sub

The code should be copied to your standard code module 1

 

[Dave Patton]

Thanks for the information.

I guess my question could have been clearer.


I want to include just the files that do not include the word "zList" within the file.

Your VBA creates a list of the files but I need only files that do not have the word "zlist" or
a sheet named "zList".

Thanks Dave

 

[JLGWhiz]

A little more complex

Sub makeFileList()
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 & "*.xl*")
    Do
        If fName = ThisWorkbook.Name Then GoTo SKIP:
            Set wb = Workbooks.Open(fPath & fName)
                flag = False
                    For Each ws In wb.Sheets
                        If ws.UsedRange.Rows.Count > 1 Then
                            Set fn = ws.UsedRange.Find("ZList", , xlValues, xlPart)
                                If Not fn Is Nothing Then
                                    flag = True
                                    Exit For
                                End If
                        End If
                    Next
                If flag = False Then
                    sh.Cells(Rows.Count, 1).End(xlUp)(2) = fName
                    wb.Close False
                    flag = False
                End If
SKIP:
            fName = Dir
    Loop While fName <> ""
End Sub

 

[JLGWhiz]

Had a line out of place. Use this instead.

 Sub makeFileList()
 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 & "*.xl*")
     Do
         If fName = ThisWorkbook.Name Then GoTo SKIP:
             Set wb = Workbooks.Open(fPath & fName)
                 flag = False
                     For Each ws In wb.Sheets
                         If ws.UsedRange.Rows.Count > 1 Then
                             Set fn = ws.UsedRange.Find("ZList", , xlValues, xlPart)
                                 If Not fn Is Nothing Then
                                     flag = True
                                     wb.Close False
                                     Exit For
                                 End If
                         End If
                     Next
                 If flag = False Then
                     sh.Cells(Rows.Count, 1).End(xlUp)(2) = fName
                     wb.Close False                     
                 End If
               flag = False
 SKIP:
             fName = Dir
     Loop While fName <> ""
 End Sub

 

[Dave Patton]

The Sub gave an error "Variable Not defined" at fn=

Then the sub indicated
This workbook contains one or more links that cannot be updated.


Thanks for your help.

I can work out a solution with a bit of brute force.

list all files
list files that contain the word from File Explorer
compare the lists with countif
Filter the results.

 

[JLGWhiz]

The Sub gave an error "Variable Not defined" at fn=

Then the sub indicated
This workbook contains one or more links that cannot be updated.

add to declarations: fn As Range

This alert is generated from one of your workbooks being opened which has links and can be stifled by adding at the top of the macro after declarations

Application.DisplayAlerts = False

and before the End Sub line insert

Application.DisplayAlerts = True

to turn them back on.

 

[Dave Patton]

The sub processed 20 files and then stopped.

Message was Run Time error 13. Type mismatch.


If it is feasible, a more reliable approach may be to search for the Worksheet name. The information that I need is always on the sheet named "zList".

My goal was to find which Workbooks do not have the summary information on a sheet
named "zList".

It seems to be awkward in an Excel file to search for the Worksheet name.
File Explorer seems to be able to search for text including Worksheet names.
My initial brute force method was not completely accurate; consequently,
I refreshed Windows 10 search index.

 

[JLGWhiz]

The sub processed 20 files and then stopped.

Message was Run Time error 13. Type mismatch.


If it is feasible, a more reliable approach may be to search for the Worksheet name. The information that I need is always on the sheet named "zList".

My goal was to find which Workbooks do not have the summary information on a sheet
named "zList".

It seems to be awkward in an Excel file to search for the Worksheet name.
File Explorer seems to be able to search for text including Worksheet names.
My initial brute force method was not completely accurate; consequently,
I refreshed Windows 10 search index.

There is a difference between looking for a text like zList in a file and looking for a sheet in a file named "zList". The OP indicated that files which did not contain the text zList should be listed. That required a search of data within each sheet of each file. Now the goal seems to be to find a sheet named "zList" to determine if a summary is on the sheet. If you can be specific in what you want as a result and what the actual criteria is, maybe I can produce the code to get you there.

 

[Dave Patton]

My goal is to determine which files in a folder Do Not have a worksheet named "zList".

The worksheet "zList" is the last file in each workbook and the relevant workbooks are xlsm.

Thanks for your help. Dave