Creating a Table from .FoundFiles

[DeutchBose]

I'm using the following code (which I found in Ms Access Help) to find file names in a shared directory...

Set fs = Application.FileSearch
With fs
.LookIn = "L:\Organize\Reconciliation"
.FileName = "Comprehensive*.xls"

If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If

Is there any way to take all the '.FoundFiles' and put them into a table?

i.e. something like this:

Title
Comprehensive1.xls
Comprehensive2.xls
Comprehensive3.xls

Thanks

 

[Norie]

This is perfectly possible.

Can you give more information?

Does the table already exist? Or do you want to create it?

Do you only want the filename? I think .FoundFiles returns the full path.

 

[Norie]

Perhaps something like this:

Const MyPath = "C:\Papers\"

Sub CreateFileListTable()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Dim I As Integer

    Set db = CurrentDb
    
    Set tdf = db.CreateTableDef("FileTable")
    
    Set fld = tdf.CreateField("Path", dbText, 255)
    
    tdf.Fields.Append fld
    
    Set fld = tdf.CreateField("FileName", dbText, 255)
    
    tdf.Fields.Append fld
    
    db.TableDefs.Append tdf
    
    Set rst = db.OpenRecordset("FileTable")
    
    With Application.FileSearch
        .LookIn = MyPath
        .FileType = msoFileTypeExcelWorkbooks
        .NewSearch
        .Execute
        .SearchSubFolders = False
        For I = 1 To .FoundFiles.Count
            
            rst.AddNew
            rst.Fields("Path") = MyPath
            rst.Fields("FileName") = Mid(.FoundFiles(I), Len(MyPath) + 1)
            rst.Update
            
        Next I
    End With
    
    rst.Close
    
    Set rst = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing

End Sub

If you already have a table you can remove the part that creates a new one.

Please note you will have to have references to the DAO and Office object libraries.

 

[DeutchBose]

Thank you very much! That helps immensely!