Loop through directories looking for files

[Sarge75]

I have a table in a DB that contains about 75 network directories. I would like to create a function that will scan all the directories and return the file name and path for all files that have a last modified equal to 2/12/2021. I would like those files that meet the criteria to be written to another existing table in the same DB.

Can anyone offer me direction to get this accomplished?

 

[Micron]

You would loop over your table records and use the directory path as a starting point. Then you would have to loop over each file in that directory folder and check if the file property (modified date) meets your criteria. Not sure what you want to write to the other table but I presume it is the complete path to the files whose date meets the criteria. That would require running append sql (INSERT INTO...) within the inner loop to append to your other table. When no more files meet the criteria the inner loop would exit and the outer loop would then move on to the next directory path that is stored in your first mentioned table. The inner loop would then repeat until there are no more path records from the main table.

FileSystemObject is what you'll need for this. This link shows how to get the modified date and a bit that will show you how to get at the files in a directory. You will need a reference in your code project to Microsoft Scripting Runtime. Hopefully I remember that name correctly.

 

[Micron]

Forgot to post the link

DateLastModified property (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[james_lankford]

create a query in Access that selects the network directories from your table
then use the following code to open the query, loop through the query one row at a time
and print the name of the network directory
pretty sure it'll work
once you get this working change the debug.print to do something with the Dir function

vba dir function - Google Search

sub doit()
    
    dim s as string
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
     
    Set db = CurrentDb
    
    Set qdf = db.QueryDefs("what ever the name of your query is")
     
    Set rst = qdf.OpenRecordset()
    
    With rst
        Do Until .EOF
            s = rst("what ever the name of your field is")
            debug.print s
            
            .MoveNext         'Move to the next Record
        Loop
    End With
    
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    
end sub