Hi Jonathan
The following code should do this for you.
Here is some code that will return a full list of all the files:
Hope this helps
Maurice
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 could easily add the code to insert each value into a table like:
dim rstRecs as dao.recordset
set retrecs = currentdb.OpenRecordSet("tblFiles")
For i = 1 To dlist.Count
Debug.Print dlist(i)
rstRecs.AddNew
rstRecs!FileName = dlist(i)
rstrecs.Update
Next i
rstrecs.close
set rstrecs = nothing
