Discussion Locked

VBA to list files in fldrs & Subfolders

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm trying to create a procedure in Access 2000 VBA that lists all of the files in a folder and all the included subfolders and then writes the file names and paths in a table. Does anyone have any tips?

5 replies

by sl-campbell

You can create a new form, add a unbound list box to it along with a text box to input a folder name and path, and a command button to execute the below code. The below code will loop through all of the file names in a folder. There are many more properties and methods that you can use here as well. Add the code to the form's class module (in form design, click on the menu VIEW|CODE). You could also add this to a module, making it public, and call it from anywhere.

So, in the loop, either add the names to the list box, or add them to the records of a table, or create new records using a recordset object.
If you run the below code "as is" just to test it, then pass it a path and then go to the test window (from the code window click on the menu VIEW|TEST or DEBUG). You will see all the files for the folder listed there.

Private Function GetFiles(ByVal sFolder As String) As Variant

Dim fso 'FileSystemObject
Dim fldr 'As folder
Dim fList 'As Files
Dim f 'As File

Set fso = CreateObject("Scripting.FileSystemObject")

Set fldr = fso.GetFolder(sFolder)
Set fList = fldr.Files

For Each f In fList
Debug.Print f.Name
Debug.Print f.Path
Next f

End Function
If you need more help then add a comment to your QUESTION, specifying what the problem is, and I will get back to you in more detail.

This discussion is closed to new replies.