Personally I prefer FSO when working with windows folders and files.
To list files to a listbox I use:
Code:
Public Sub ListFilesListBox(SourceF As String, Lbx As ListBox, Optional IncSubFolders As Boolean = True)
'Dim fso As New FileSystemObject 'early binding
'Dim fol As Folder, sfol As Folder, fil As File
Dim fso As Object 'late binding
Set fso = CreateObject("Scripting.FileSystemObject")
Dim fol As Object, sfol As Object, fil As Object
Set fol = fso.GetFolder(SourceF)
For Each fil In fol.Files
Lbx.AddItem fil.Name & ";" & fil.Path & ";" & fil.DateLastModified & ";" & fil.Type
Next
If IncSubFolders Then
For Each sfol In fol.SubFolders
ListFilesListBox sfol.Path, Lbx, IncSubFolders
Next
End If
End Sub
When writing files to a table I use the below code. I use a dictionary to insure that no duplicate entries are added to the table.
Code:
'Dim dict As Scripting.Dictionary 'early binding
Dim dict As Object 'Late binding
Sub ListMyFilesToTable(SourceF As String, Optional IncSubFolders As Boolean = True)
PopulateDictionary
sListFilesTableNoDups SourceF, IncSubFolders
End Sub
Sub PopulateDictionary()
'Set dict = New Scripting.Dictionary 'early binding
Set dict = CreateObject("Scripting.Dictionary") 'Late binding
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim strOut As String
strSql = "Select * from tblFiles"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)
If rs.BOF And rs.EOF Then
GoTo MyExit
End If
Do Until rs.EOF
dict.Add CStr(rs!fpath), CStr(rs!fpath)
rs.MoveNext
Loop
MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
Sub sListFilesTableNoDups(SourceF As String, Optional IncSubFolders As Boolean = True)
Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb
'Dim fso As New FileSystemObject 'early binding
'Dim fol As Folder, sfol As Folder, fil As File
Dim fso As Object 'late binding
Set fso = CreateObject("Scripting.FileSystemObject")
Dim fol As Object, sfol As Object, fil As Object
Set fol = fso.GetFolder(SourceF)
For Each fil In fol.Files
If Not dict.Exists(fil.Path) Then
strSql = "Insert into tblFiles(FName,FPath,dteCreated,dteModified) Values(""" & fil.Name & """,""" & fil.Path & """,#" & fil.DateCreated & "#,#" & fil.DateLastModified & "#)"
db.Execute strSql, dbFailOnError
End If
Next
If IncSubFolders Then
For Each sfol In fol.SubFolders
sListFilesTableNoDups sfol.Path, IncSubFolders
Next
End If
Set db = Nothing
End Sub