How can I put folder contents into a table?

Hi,

Here are 2 functions that I use.  Paste the code below into a new module:



Function DoIt(vFolder As String)
    Dim vList As Variant, i As Integer, vFileNameFound As String, v
    vList = FileList(vFolder, "*.*")
    If TypeName(vList) <> "Boolean" Then
        For i = LBound(vList) To UBound(vList)
            vFileNameFound = vList(i)
            Debug.Print vFileNameFound
           'CurrentDB.Execute("INSERT INTO tablename (columnname) VALUES('" & vFileNameFound & "');"
        Next
    End If
   
End Function

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = False
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
     Loop
    FileList = Split(sTemp, "|")
End Function




Once you have that done then test it in the immediate window using:

?DoIt("C:\Windows")

This will list all the files in the Windows folder to the debug window.
Uncomment the execute command and fix the SQL statement to insert the file name into your table and column.

Regards,

Bill

Jeff, for 100,000 files I like more your code, but you should move  strFileName = Dir to the end of loop. It will be also very interesting to compare  execution time of code with execute and direct write to table:

Dim strFolderName As String
Dim strFileName As String
Dim rs as DAO.Recorset
strFolderName = "c:\Yourfolder\"

'Clear out the old file names
CurrentDb.Execute "DELETE * FROM tblFiles", dbFailOnError
Set rs = CurrentDB.OpenRecordset("tblFIles")
strFileName = Dir(strFolderName & "*.Gif")

Do While strFileName <> ""
    rs.addNew
    rs!FileName = strFileName
    rs.update
    strFileName = Dir
Loop
rs.close
Set rs = Nothing

Select allOpen in new window

als315.

LOL

You are probably correct...
That is just something I threw together in a hurry...
;-)

Jeff

Thanks guys.  Great work!