Is there a simple algorithm or snippet of VBA code
that will allow me to search for a target subfolder???
I'm only interested in finding the first occurrence of
a subfolder....so if it finds the subfolder, the code should
stop searching at that point. Once found, the code should
return the full path to that subfolder.

thank you

I assume that you want to start in some particular folder and then
search all the subfolder trees for a specific subfolder. Change the
lines marked with <<<< to the starting folder name and the subfolder
to search for. As written, the code just writes some Debug text to
indicate whether the subfolder was found. Substitute your own code in
place of the Debug statements.

You'll need a reference to the Scripting RunTime library. In VBA with
your project open, go to the Tools menu, choose References, and scroll
down to "Microsoft Scripting Runtime" and put a check next to that
entry.


Sub FindSubFolder()
Dim FSO As Scripting.FileSystemObject
Dim FF As Scripting.Folder
Dim SubF As Scripting.Folder
Dim StartFolderName As String
Dim SubFolderToFind As String
Dim S As String

StartFolderName = "C:\One\Two" '<<<<< CHANGE AS NEEDED
SubFolderToFind = "FindMe" '<<<< CHANGE AS NEEDED
Set FSO = New Scripting.FileSystemObject
Set FF = FSO.GetFolder(StartFolderName)

For Each SubF In FF.SubFolders
If StrComp(SubF.Name, SubFolderToFind, vbTextCompare) = 0 Then
Debug.Print "FOUND: " & FF.Path
Exit Sub
Else
S = FindSubFolder(SubFolderToFind, SubF)
If Len(S) 0 Then
Debug.Print "FOUND: " & S
Exit Sub
End If
End If
Next SubF
Debug.Print "not found"
End Sub


Function FindSubFolder(FolderName As String, _
SubFolder As Scripting.Folder) As String
Dim SubF As Scripting.Folder
Dim S As String
If StrComp(FolderName, SubFolder.Name, vbTextCompare) = 0 Then
FindSubFolder = SubFolder.Path
Exit Function
Else
For Each SubF In SubFolder.SubFolders
S = FindSubFolder(FolderName, SubF)
If Len(S) 0 Then
FindSubFolder = S
Exit Function
End If
Next SubF
End If
End Function

You'll notice that the function FindSubFolder calls itself to traverse
the subfolder trees. This technique is called "recursion". See
http://www.cpearson.com/excel/RecursiveProgramming.aspx and
http://www.cpearson.com/excel/RecursionAndFSO.htm for information and
examples of recursive programming.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Fri, 12 Feb 2010 15:07:59 -0700, "Robert Crandal"
wrote:

Is there a simple algorithm or snippet of VBA code
that will allow me to search for a target subfolder???
I'm only interested in finding the first occurrence of
a subfolder....so if it finds the subfolder, the code should
stop searching at that point. Once found, the code should
return the full path to that subfolder.

thank you