Tea -
Yes it can be done, you'll need the Scripting Runtime to do it.
Here's a block of code I stole, tweaked and rewrote to pull certain
info from files in a given folder into a table. On the form, if you
check the SubFolders box you activate the "recursive" portion of the
code.
It's also looking at specs I define in rec2, which is a table where I
determine which file extentions I want, between which dates, and if I
want to limit the results to files of a minimum size. All those
references can be ignored since you probably don't care about them.
You'll be most interested in FileName and FilePath to achieve your
results.
In your case, strPath would be "C:\My Documents", but you can add a
textbox to a form and pass any path you want into it. blnRecursive
would be "True".
Public Function InsertFiles(strPath As String, Optional blnRecursive As
Boolean)
' This procedure returns all the files in a directory into
' a Dictionary object. If called recursively, it also returns
' all files in subfolders.
' Requires Microsoft Scripting Runtime reference
Dim rec, rec2
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnn = CodeProject.Connection
Dim db As Database
Set db = CurrentDb
Set rec = db.OpenRecordset("tblFiles")
Set rec2 = db.OpenRecordset("tblParams")
Dim fsoSysObj As Scripting.FileSystemObject
Dim z
Dim fdrFolder As Scripting.Folder
Dim fdrSubFolder As Scripting.Folder
Dim filFile As Scripting.File
'Dim strSQL As String
'Check the Last Accessed date
If IsNull(rec2("DateLastAccessedGT")) Then
rec2.Edit
rec2("DateLastAccessedGT") = #1/1/1900#
rec2.Update
End If
If IsNull(rec2("DateLastAccessedLT")) Then
rec2.Edit
rec2("DateLastAccessedLT") = Date
rec2.Update
End If
If IsNull(rec2("DateLastModifiedGT")) Then
rec2.Edit
rec2("DateLastModifiedGT") = #1/1/1900#
rec2.Update
End If
If IsNull(rec2("DateLastModifiedLT")) Then
rec2.Edit
rec2("DateLastModifiedLT") = Date
rec2.Update
End If
' Return new FileSystemObject.
Set fsoSysObj = New Scripting.FileSystemObject
' Get folder.
Set fdrFolder = fsoSysObj.GetFolder(strPath)
' Loop through Files collection
For Each filFile In fdrFolder.Files
If Not IsNull(filFile.Size) Then
If filFile.Size / 1000 < rec2("FileSize") Then
GoTo NotIn
End If
End If
If DateValue(rec2("DateLastAccessedGT")) <=
DateValue(filFile.DateLastAccessed) Then
If DateValue(filFile.DateLastAccessed) <=
DateValue(rec2("DateLastAccessedLT")) Then
' If DateValue(rec2("DateLastAccessedGT")) <=
DateValue(Left(filFile.DateLastAccessed,
InStr(filFile.DateLastAccessed, " "))) Then
' If DateValue(Left(filFile.DateLastAccessed,
InStr(filFile.DateLastAccessed, " "))) <=
DateValue(rec2("DateLastAccessedLT")) Then
Else
GoTo NotIn
End If
Else
GoTo NotIn
End If
'Check the Last Modified date
If DateValue(rec2("DateLastModifiedGT")) <=
DateValue(filFile.DateLastModified) Then
If DateValue(filFile.DateLastModified) <=
DateValue(rec2("DateLastModifiedLT")) Then
' If DateValue(rec2("DateLastModifiedGT")) <=
DateValue(Left(filFile.DateLastModified,
InStr(filFile.DateLastModified, " "))) Then
' If DateValue(Left(filFile.DateLastModified,
InStr(filFile.DateLastModified, " "))) <=
DateValue(rec2("DateLastModifiedLT")) Then
Else
GoTo NotIn
End If
Else
GoTo NotIn
End If
If rec2("FileType") <> "All Files" Then
If Not IsNull(filFile.Type) And filFile.Type <> rec2("FileType")
Then
GoTo NotIn
End If
End If
With rec
.AddNew
!FilePath = Left(filFile.Path, Len(filFile.Path) -
Len(filFile.Name))
!FileName = filFile.Name
!FileSize = filFile.Size / 1000
!FileType = filFile.Type
' !FileVer = VersionInformation(filFile.Path)
!DateLastAccessed = filFile.DateLastAccessed
!DateLastModified = filFile.DateLastModified
.Update
End With
Forms!frmFiles.lblDisplayFilePath.Caption = filFile.Path
Forms!frmFiles.lblDisplayFileSize.Caption = filFile.Size / 1000
Forms!frmFiles.lblDisplayFileType.Caption = filFile.Type
Forms!frmFiles.lblDisplayDateLastAccessed.Caption =
filFile.DateLastAccessed
Forms!frmFiles.lblDisplayDateLastModified.Caption =
filFile.DateLastModified
intCounter = intCounter + 1
Forms!frmFiles.lblDisplayRecordsProcessed.Caption = intCounter
Forms!frmFiles.Repaint
DoEvents
NotIn:
Next filFile
cnn.Close
' If Recursive flag is true, call recursively.
If blnRecursive = True Then
For Each fdrSubFolder In fdrFolder.SubFolders
Call InsertFiles(fdrSubFolder.Path, True)
Next fdrSubFolder
End If
Set cnn = Nothing
End Function
