List Folders using Excel
 

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

List Folders using Excel
 

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

List Folders using Excel
 

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

List Folders using Excel
 

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