Try:
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
You can of couse add 3 or 4 lines of code to the dirtest code to write this
data to a table..
