CODEWITHSUNDEEP
excelvbawildcard
Ryan Hare
Ryan Hare

Reputation: 31

How to search a folder for a document with variable file type with "*" wildcard?

I want to search for a document in a folder and return its location.

The document can be either .xlsx or .pdf; therefore, I want to add "*" to the name to find either.

Sub Test2()
    Dim FSO As New FileSystemObject
    Dim myFolder As Scripting.Folder
    Dim mySubFolder As Scripting.Folder
    Dim myFile As File

    Set myFolder = FSO.GetFolder("Folder Path")
    Set Team3 = ThisWorkbook.Worksheets("Team 3 & 3a")
    Rng = Team3.Range("B4:B9")
    
    For Each batch In Rng    
        Filename = batch & "*"

        For Each mySubFolder In myFolder.SubFolders        
            For Each myFile In mySubFolder.Files            
                If myFile.Name = Filename Then                
                    MsgBox myFile.Path
                    Exit For                    
                End If                
            Next            
        Next               
    Next
End Sub

The wildcard * seems to be interpreting it as text. If I replace the "*" with ".pdf" it does find the files, but I need to search for any type of file.

I removed the folder path from the above, but it is searching in the correct folder. The code works, its just the "*" that doesn't seem to work.

Upvotes: 0

Views: 119

Answers (1)

Ryan Hare
Ryan Hare

Reputation: 31

Thank you, FunThomas for helping with this. Not sure how this site works, but the following was the answer given by him.

Sub Test2()    
    Dim FSO As New FileSystemObject
    Dim myFolder As Scripting.Folder
    Dim mySubFolder As Scripting.Folder
    Dim myFile As File

    Set myFolder = FSO.GetFolder("Folder Path")
    Set Team3 = ThisWorkbook.Worksheets("Team 3 & 3a")
    Rng = Team3.Range("B4:B9")

    For Each batch In Rng        
        Filename = batch & "*"
    
        For Each mySubFolder In myFolder.SubFolders            
            For Each myFile In mySubFolder.Files                
                If myFile.Name Like Filename Then                    
                    MsgBox myFile.Path
                    Exit For                        
                End If                    
            Next                
        Next                    
    Next        
End Sub

Upvotes: 1

Related Questions