Re-creating tables from a list of files

[Dion Warren]

Hello All,

I am trying to have a "workbook list" table recreate
itself based off what files are in a particular folder.
For example, if I have file 1, file 2, and file 3 saved in
a folder then I would like my "workbook list" table show
that. Then if the files in the folder are changed to
consist of file 2, file 4, and file 5 then I would like
the "workbook" list table show only those file names. Is
this possible - any ideas?

Thanks,
Dion

 

[Nikos Yannacopoulos]

Dion,

It's quite easy with a small VB procedure. In my example below I'll assume
the name of the table to be tblFiles, with fields:
FName (text)
Created (Date)
Modified (Date)
Size (Long)

Change to the actual table name, and modify the For loop part if the number
/ sequence of your table fields is different:

Sub Read_Files_In_Folder()
Dim rs As DAO.Recordset
strSQL = "DELETE * FROM tblFiles"
CurrentDb.Execute strSQL
Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder("C:\temp\")
Set fls = fldr.Files
Set rs = CurrentDb.OpenRecordset("tblFiles")
For Each fl In fls
rs.AddNew
rs.Fields(0) = fl.Name
rs.Fields(1) = fl.DateCreated
rs.Fields(2) = fl.DateLastModified
rs.Fields(3) = fl.Size \ 1024 + 1
rs.Update
Next fl
rs.Close
Set rs = Nothing
End Sub

Note: you will need an appropriate DAO reference for this code to work;
while in the VBA window, go Tools > References, and look if a Microsoft DAO
3.XX Object Library is checked; if not, scroll down to find it and check it.
Select DAO 3.51 for A97, DAO 3.6 for A2K or newer.

HTH,
Nikos

 

[Guest]

Thank you very much Nikos - that worked perfectly!!!