Find Text in folder directory

[ajit80]

Hello everyone,

Pls Help,

I have a folder directory (Name: "Company 2009") containing excel files:-

We want to find Name of File Containing Text "No Ceiling" and show a list of File Names having this content.

Thanking You

 

[KKaren]

Hi,

Try this - just change the complete location of the relevant folder in red. The file names should be printed in the immediate window.

Sub CheckFileNames()
    
    Dim fso As Object
    Dim FileToCheck As Object
    Dim FolderToCheck As Object
    Dim FolderToCheckPath As String
    
    FolderToCheckPath = "C:\Users\Documents\Excel\Company 2009\"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set FolderToCheck = fso.GetFolder(FolderToCheckPath)

    For Each FileToCheck In FolderToCheck.Files
    
        If FileToCheck.Name Like "*No Ceiling*" Then
        
            Debug.Print FileToCheck.Name
            
        End If
        
    Next FileToCheck
    


End Sub

 

[Yongle]

Be aware @KKaren solution is case sensitive

it lists only "No Ceiling"
it does not list "no ceiling", "No Ceiling","No ceiling","NO CEILING" etc

This test is case sensitive

        If FileToCheck.Name Like "*No Ceiling*" Then

If you do not want the list case sensitive, here is one way to modify the test

        If [B]UCase([/B]FileToCheck.Name[B])[/B] Like "*[B]NO CEILING[/B]*" Then

 

[Kenneth Hobson]

The way I read post #1 was to search for content inside the file and not as part of a file name.

 

[ajit80]

Thanks KKaren,

But code is not working. Not showing any file name which containing text "No Ceiling". I have lots of file where content (No Ceiling) inside the file.

Main Folder Directory: Company 2009
sub folders: A, B, C, ......................Z
Sub Folders contains many .xls Files.