List files recursively

[rick_it]

Can someone help me to modify the following code to write the files to a
table. At the moment the code allows me to use the "Immediate window" or
"list the files in a box". I need to write them on a table. I understand I
have to modify the ListFiles() function, OpenRecordset, and AddNew with
Update as I loop through the items...however, I'm lost! :-(

Thanks

Rick

Here is the code and the link where I found it:
http://allenbrowne.com/ser-59.html


Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from
subdirectories of strPath as well.
' lst: if you pass in a list box, items are added to it. If
not, files are listed to immediate window.
' The list box must have its Row Source Type property set
to Value List.
'Method: FilDir() adds items to a collection, calling itself
recursively for subfolders.
Dim colDirList As New Collection
Dim varItem As Variant

Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)

'Add the files to a list box if one was passed in. Otherwise list to the
Immediate Window.
If lst Is Nothing Then
For Each varItem In colDirList
Debug.Print varItem
Next
Else
For Each varItem In colDirList
lst.AddItem varItem
Next
End If

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
....

 

[Damian S]

Hi rick_it,

Replace the code that has Debug.Print varItem with something similar to the
following:

docmd.runsql "insert into TABLENAME(FIELDNAME) values ('" & varItem & "')"

You might need to grab a specific property out of varItem to get the
filename or whatever, but the basic syntax is correct.

Damian.

 

[rick_it]

Damian,

thanks so much. Hopefully I'll be able to make it now. I will try tomorrow
from work.