Looping through subfolders in a folder.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to start in a folder and have VBA loop through the folders within
that folder...

Start in myFolder
goto first subfolder
import *xls File
goto next folder
import xls file

until no more folders.
The Excel part I've got, I just can't seem to figure out the code.... So
far.....

DIM yadda yadda..

Set strFolderNames = "path to my primary folder"
set fso = CreateObject("Scripting.filesystemobject")

I'm lost after that......

Help me Obi Wan Kanobi... You're my only hope.

Crusty.
 
I used to know how to do this - what you need to do is program what is known
as a recurisve search. I only did it once, at it was years ago. Sorry.

If you do a search in these newsgroups for "recursive search", you should
find something to point you in the right direction.
 
Albert Kallal (ACCESS MVP) posted code to do just this type of thing back in
April 2005 in this newsgroup:
----------------
Here is a nice short routine that will "walk" the dir structure...

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub


Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub
 
Back
Top