Hi All,

I am trying to build a routine that will recursively search through all
folders and subfolders for a given path and list all folders.

I have found exmples of how to list all files but not only the folders.

Any ideas?

TIA

Andy

For that you need the FileSystemObject. try the code below:

CODE
Sub recursiveFolderSearch(ByVal sPath As String)

Dim fs As Object
Dim oTopFolder As Object
Dim oFolder As Object
Dim oSubFolders
Dim oSubFolder

Set fs = VBA.CreateObject("Scripting.Filesystemobject")
Set oTopFolder = fs.GetFolder(sPath)

For Each oFolder In oTopFolder.subfolders
Range("A1").End(xlDown).Value = oFolder.Path
If oFolder.subfolders.Count 0 Then
Call recursiveFolderSearch(oFolder.Path)
End If
Next

End Sub

Sub recursiveList()

Call recursiveFolderSearch("c:\program files)

End Sub
<<<< END CODE <<<<

HTH

Philip

"Andibevan" wrote:

Hi All,

I am trying to build a routine that will recursively search through all
folders and subfolders for a given path and list all folders.

I have found exmples of how to list all files but not only the folders.

Any ideas?

TIA

Andy

Andy,

In VBA, go to the Tools menu, choose References, and put a check
next to Microsoft Scripting Runtime. Then, use the following
code:

Sub Start()
Dim FSO As Scripting.FileSystemObject
Dim Rng As Range
Set FSO = New Scripting.FileSystemObject
Set Rng = Range("A1")
Const cSTART_FOLDER = "H:\ExcelProjects" '<<< CHANGE
ListFiles FSO.GetFolder(cSTART_FOLDER), Rng
End Sub

Sub ListFiles(Fldr As Scripting.Folder, Rng As Range)
Dim F As Scripting.File
Dim FF As Scripting.Folder
For Each F In Fldr.Files
Rng.Value = F.Path
Set Rng = Rng(2, 1)
Next F

For Each FF In Fldr.SubFolders
ListFiles FF, Rng
Next FF
End Sub


"Andibevan" wrote in message
...
Hi All,

I am trying to build a routine that will recursively search
through all
folders and subfolders for a given path and list all folders.

I have found exmples of how to list all files but not only the
folders.

Any ideas?

TIA

Andy

Thanks for both the suggestions - works a dream

"Chip Pearson" wrote in message
...
Andy,

In VBA, go to the Tools menu, choose References, and put a check
next to Microsoft Scripting Runtime. Then, use the following
code:

Sub Start()
Dim FSO As Scripting.FileSystemObject
Dim Rng As Range
Set FSO = New Scripting.FileSystemObject
Set Rng = Range("A1")
Const cSTART_FOLDER = "H:\ExcelProjects" '<<< CHANGE
ListFiles FSO.GetFolder(cSTART_FOLDER), Rng
End Sub

Sub ListFiles(Fldr As Scripting.Folder, Rng As Range)
Dim F As Scripting.File
Dim FF As Scripting.Folder
For Each F In Fldr.Files
Rng.Value = F.Path
Set Rng = Rng(2, 1)
Next F

For Each FF In Fldr.SubFolders
ListFiles FF, Rng
Next FF
End Sub


"Andibevan" wrote in message
...
Hi All,

I am trying to build a routine that will recursively search
through all
folders and subfolders for a given path and list all folders.

I have found exmples of how to list all files but not only the
folders.

Any ideas?

TIA

Andy