Excel Macro That searches for existence of Folder.

[tommyt61]

I ran across Ken's code that will search for a folder. It works great , but i am curious if it could be adjusted a little. Currently the code looks for a folder entered into the code. In the code below it loks to see if C:\Template exists. I bad need to be able to enter text or data into say cell K10 and the code will look for a folder that contains any part of the data or text entered into this cell.

Example:

I will need code to start at this base folder. N:\Quotes

we will say i have a subfolder named BR549. FULL PATH N:\Quotes\BR549

i would like to be able to enter BR5 IN CELL K10 AND THE CODE will start at N:\Quotes and look for and list all folders that start with BR5.

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
 
EarlyExit:
    On Error GoTo 0
End Function
Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
 
 
    If FileFolderExists("C:\Template") Then
        MsgBox "Folder exists!"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

[tommyt61]

I figured out how to make a variable out a cell value . Can anyone help with the rest?

Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
    iMyvalue = Range("B2").Value

    ChDir ("C:\")
    If FileFolderExists("C:\" & (iMyvalue)) Then
        MsgBox "Folder exists!"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

[Bob Phillips]

Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
Dim MyValue As String
Dim msg As String
Dim fldr As Object

    MyValue = Range("B2").Value

    ChDir ("C:\")
    If FileFolderExists("C:\" & MyValue) Then
        
        With CreateObject("Scripting.FileSystemobject").GetFolder("C:\" & MyValue)
        
            For Each fldr In .Subfolders
            
                msg = msg & fldr.Name & vbNewLine
            Next fldr
        End With
        
        MsgBox msg, vbOKOnly, "All folders"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

[tommyt61]

Bob, that works perfectly. i need to add a little more functioality to it. i need to be able to enter just part of a folder name and it can use wildcards in the code to find all folders. say i have a folder i created named BR549AC4. Several months go by , i can't remember the full name but i do know that it starts with BR5. I would like to be able to enter BR5 and it would list all folders it finds that contain BR5 I listed the code below i have been experimenting with but have not had any luck. Can you help ?
Thanks so much

Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
Dim MyValue As String
Dim msg As String
Dim fldr As Object
    MyValue = Range("B2").Value
    ChDir ("C:\")
    'If FileFolderExists("C:\" & MyValue) Then
 
   If FileFolderExists("C:\" & "*" & MyValue & "*") Then
      '"*" & (iMyvalue) & "*"
        With CreateObject("Scripting.FileSystemobject").GetFolder("C:\" & "*" & MyValue & "*")
 
            For Each fldr In .Subfolders
 
                msg = msg & fldr.Name & vbNewLine
            Next fldr
        End With
 
        MsgBox msg, vbOKOnly, "All folders"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

[Bob Phillips]

Public Function FileFolderExists(strFullPath As String) As String
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    FileFolderExists = Dir(strFullPath, vbDirectory)
 
EarlyExit:
    On Error GoTo 0
End Function

Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
Dim MyValue As String
Dim subFldr As Object
Dim msg As String
Dim fldr As String

    MyValue = Range("B2").Value
    ChDir "C:\"
    fldr = FileFolderExists("C:\*" & MyValue & "*")
 
    If fldr <> "" Then
    
        For Each subFldr In CreateObject("Scripting.FileSystemobject").GetFolder(fldr).Subfolders
 
            msg = msg & subFldr.Name & vbNewLine
        Next subFldr
 
        MsgBox msg, vbOKOnly, "All folders"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

Note that I havce changed both procedures.

[tommyt61]

Bob, that is working great. Can you see if you could make a slight adjustment. It is so hard at times to explain correctly what you need code to do. I'll do my best to explain below. Thanks so much for your help.

Say i have this folder structure ( all the br* folders are subfolders of C:\Quotes)

C:\Quotes\br549
br540
br54786
br6ty6
br8hr34


I'll set the path in the code to C:\Quotes
if i enter br5 into Cell B2 it will list all these folders

br549
br540
br54786

i need to search all subfolders of C:\Quotes for all folders that contain br5 and it list them.

[Bob Phillips]

Is this what you mean?

Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
Dim MyValue As String
Dim subFldr As Object
Dim msg As String
Dim fldr As String

    MyValue = Range("B2").Value
    ChDir "C:\"
    fldr = "C:\test"
 
    If Dir(fldr, vbDirectory) <> "" Then
    
        For Each subFldr In CreateObject("Scripting.FileSystemobject").GetFolder(fldr).Subfolders
 
            If subFldr.Name Like "*" & MyValue & "*" Then msg = msg & subFldr.Name & vbNewLine
        Next subFldr
 
        MsgBox msg, vbOKOnly, "All folders"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

[tommyt61]

That is exactly what i need !

Thanks so much for your help.

[dylana]

If I could piggyback on this I could use the help. I've got a good grasp of what the code is doing, Bob, but wonder if it can be tweaked a bit.

I'm trying to accomplish something similar. For my code, I want to be able to search a specific folder that will be specified in the code. The folder has multiple subfolders and I want to search those subfolders for another subfolder within based on a name that the code will also know.

How might that code look? I'm not trying to verify if a folder exists, but rather have the code locate the folder within a couple tiers of subfolders. The reasoning is that the folder I'm looking for is saved in a temporary internet folder that can change names, although the main outer folder path is always the same.

Thanks