Looping through subfolders in a folder.

C

CptCrusty1

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.
 
M

MDW

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.
 
K

Ken Snell [MVP]

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
 
Top