A Looping Funtion to list files in folders

[Greg Maxey]

The following is a condensed version of a larger piece of code that I have
been working on to list files in folders. Currenty it will drill down
looking for files two sub-folder layers deep. What I have spent half the
day on is some method of looping through a single procedure or funtion to
drill down to the last sub-folder layer in a given folder.

I figured out how to do that if each folder only has one subfolder, but that
isn't practical. Thanks for any assistance, ideas, or even a statement that
it just can't be done.


Option Explicit
'Set reference to Microsoft Scripting Runtime
Public fso As New FileSystemObject
Dim oFld As Folder
Dim oFile As File
Dim oRng As Word.Range
Sub ListFolder()
Dim sPath As String
sPath = "C:\Batch"
Set oFld = fso.GetFolder(sPath)
For Each oFile In oFld.Files
Debug.Print oFile.Name
Next oFile
If oFld.SubFolders.Count > 0 Then
ListSubFolders oFld.SubFolders
End If
End Sub
Function ListSubFolders(ByRef oSubFolders As Folders)
Dim oSubFolder As Scripting.Folder
For Each oSubFolder In oSubFolders
For Each oFile In oSubFolder.Files
Debug.Print oSubFolder.Name & " " & oFile.Name
Next oFile
If oSubFolder.SubFolders.Count > 0 Then
ListSubFolders2 oSubFolder.SubFolders
End If
Next oSubFolder
Set oSubFolder = Nothing
End Function
Function ListSubFolders2(ByRef oSubFolders As Folders)
Dim oSubFolder2 As Scripting.Folder
For Each oSubFolder2 In oSubFolders
For Each oFile In oSubFolder2.Files
Debug.Print oSubFolder2.Name & " "; oFile.Name
Next oFile
Next oSubFolder2
Set oSubFolder2 = Nothing
End Function


--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org


McCain/Palin '08 !!!

 

[Helmut Weber]

Hi Greg,

see: http://word.mvps.org/faqs/macrosvba/ReadFoldersIntoArray.htm

or, unless you are insisting on doing it the hard way,
in the cmd shell from your start folder

dir *.* /b/s > c:\test\dir.txt

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[Steve Yandl]

Greg,

The articles below give different approaches to a recursive subroutine being
used to fetch an entire subfolder tree (plus file contents in one case).
They're both for vbScript files but I don't think you'll have any trouble
adapting for VBA. The first uses the scripting filesystemobject as you've
started to do. The second uses WMI. The advantage to the WMI approach is
that it would allow you to fetch information from a remote computer on your
network but if you're working strictly with a local PC, I find the
filesystemobject approach easier to understand.

http://www.microsoft.com/technet/scriptcenter/resources/qanda/oct04/hey1020.mspx

http://www.microsoft.com/technet/scriptcenter/resources/qanda/jan07/hey0126.mspx


Steve Yandl

 

[Greg Maxey]

Helmut, Steve

Good reading all. Thanks for the nudge.

I decided on the fso method. Here is a simple working VBA demo:

Note: change C:Batch to any folder on your PC.

Option Explicit
'Add reference to Microsoft Scripting Runtime
Dim fso As New FileSystemObject
Dim colFolders As New Collection
Sub Demo()
Dim oFld As Folder
Dim lngC As Long
Set oFld = fso.GetFolder("C:\Batch")
MsgBox "Do something with: " & oFld
CollectSubFolders oFld
If colFolders.Count > 0 Then
For lngC = 1 To colFolders.Count
MsgBox "Do something with " & colFolders(lngC)
Next
End If
Set colFolders = Nothing
End Sub
Sub CollectSubFolders(oFolder As Folder)
Dim SubFolder As Folder
For Each SubFolder In oFolder.SubFolders
colFolders.Add SubFolder.Path, SubFolder.Path
Set oFolder = fso.GetFolder(SubFolder.Path)
CollectSubFolders SubFolder
Next
End Sub

The following is a condensed version of a larger piece of code that I
have been working on to list files in folders. Currenty it will
drill down looking for files two sub-folder layers deep. What I have
spent half the day on is some method of looping through a single
procedure or funtion to drill down to the last sub-folder layer in a
given folder.
I figured out how to do that if each folder only has one subfolder,
but that isn't practical. Thanks for any assistance, ideas, or even
a statement that it just can't be done.


Option Explicit
'Set reference to Microsoft Scripting Runtime
Public fso As New FileSystemObject
Dim oFld As Folder
Dim oFile As File
Dim oRng As Word.Range
Sub ListFolder()
Dim sPath As String
sPath = "C:\Batch"
Set oFld = fso.GetFolder(sPath)
For Each oFile In oFld.Files
Debug.Print oFile.Name
Next oFile
If oFld.SubFolders.Count > 0 Then
ListSubFolders oFld.SubFolders
End If
End Sub
Function ListSubFolders(ByRef oSubFolders As Folders)
Dim oSubFolder As Scripting.Folder
For Each oSubFolder In oSubFolders
For Each oFile In oSubFolder.Files
Debug.Print oSubFolder.Name & " " & oFile.Name
Next oFile
If oSubFolder.SubFolders.Count > 0 Then
ListSubFolders2 oSubFolder.SubFolders
End If
Next oSubFolder
Set oSubFolder = Nothing
End Function
Function ListSubFolders2(ByRef oSubFolders As Folders)
Dim oSubFolder2 As Scripting.Folder
For Each oSubFolder2 In oSubFolders
For Each oFile In oSubFolder2.Files
Debug.Print oSubFolder2.Name & " "; oFile.Name
Next oFile
Next oSubFolder2
Set oSubFolder2 = Nothing
End Function

--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org


McCain/Palin '08 !!!