Excel VBA - Find a sub-sub folder containing a string, using more than one wildcard

[Tom Huntford]

I searched and searched for a way to find and open a folder that contains a certain string. The key was HERE, thanks so much to Jerry Sullivan.

Beyond that, my problem was, that I needed to find a string in a folder name with one wildcard, then within it, find a sub-folder with another string, using another wildcard.

I found out:

1. You can't use a wildcard in the middle of a file path string.
2. And so, you can't use two wildcards in the same filepath string.

So I had to get the first part of the path set up, with the wildcard at the end, save that name in a variable, then set up the next piece of the path using another wildcard at the end of the path. Success!--thanks be to God my Savior! This could be done any number of times. Below is my code. I have left the message boxes in so you can check your filepath build as you go along. Of course take them out if needed. At the end, is a line that opens up the folder in Windows Explorer. This is because, in my case, there can be revisions to the file by Engineering, with R1, R2,etc added, so I can't open the file directly. If you had a file name that was always the same, you could add it to the end of the final string, skip opening Explorer, open the file directly, and go for it...

Sub Find_SubFolderWithMoreThanOneWildcardLevel()

    Dim sFile As String, sPathSeek As String, sPathMatch As String
    
    Const sMainPath As String = "V:\jobs\"
                
   Dim sOrder As String 'THIS IS THE FIRST STRING I NEED A WILDCARD TO FIND
   sOrder = InputBox("Please enter the Order #, like 2205, 2358", "Enter Order #") 
   Dim sSection As String
   sSection = InputBox("Please enter the Section #, like 103, 241", "Enter Section #")
   Dim sOrdSec As String 'THIS IS THE SECOND STRING I NEED A WILDCARD TO FIND
   sOrdSec = sOrder & "-" & sSection 
    
    
'FIND THE FOLDER THAT CONTAINS THE ORDER #, USING A WILDCARD
    On Error Resume Next
    sPathSeek = sMainPath & sOrder & "*.*"
    MsgBox sPathSeek
    sFile = Dir(sPathSeek, vbDirectory)


    Do While Len(sFile) > 0
        If Left(sFile, 1) <> "." Then
            If (GetAttr(sFile) And vbDirectory) = vbDirectory Then
                sPathMatch = sFile
                Exit Do
            End If
        End If
        sFile = Dir
    Loop


    MsgBox IIf(sPathMatch = "", "Match not found", "Match: " & sPathMatch)
    
'FIND THE SUBFOLDER THAT CONTAINS THE STRING "JOB#-SECTION#", USING A WILDCARD
 Dim sFile2 As String, sPathSeek2 As String, sPathMatch2 As String
    
 On Error Resume Next
    sPathSeek2 = sMainPath & sPathMatch & "\Mechanical\" & sOrdSec & "*.*"
    MsgBox sPathSeek2
    sFile2 = Dir(sPathSeek2, vbDirectory)


    Do While Len(sFile2) > 0
        If Left(sFile2, 1) <> "." Then
            If (GetAttr(sFile2) And vbDirectory) = vbDirectory Then
                sPathMatch2 = sFile2
                Exit Do
            End If
        End If
        sFile2 = Dir
    Loop


    MsgBox IIf(sPathMatch2 = "", "Match not found", "Match: " & sPathMatch2)
    
Dim SecondLevelPath As String
    
'CONCATENATED PATH THAT INCLUDES TWO FOLDER THAT WERE FOUND USING WILDCARDS
    SecondLevelPath = sMainPath & sPathMatch & "\Mechanical\" & sPathMatch2 & "\"
    
MsgBox SecondLevelPath
 
'OPEN THE SUB-SUB-SUB-SUB FOLDER THAT HAS THE INFORMATION WE NEED...
    FinalPath = SecondLevelPath & "Documents\BOM\"

MsgBox FinalPath
 
 Shell "C:\WINDOWS\explorer.exe """ & FinalPath & "", vbNormalFocus
     
    
End Sub