Provided by Allen Browne, June 2006 — adapted from a Usenet posting by Albert Kallal. Last updated: April 2010.
This article illustrates how to list files recursively in VBA.
Output can be listed to the immediate window, or (in Access 2002 or later) added to a list box.
See List files to a table if you would prefer to add the files to a table rather than list box.
See DirListBox() for Access 97 or earlier.
Or, Doug Steele offers some alternative solutions in Find Your Data.
To add the code to your database:
To list the files in C:\Data, open the Immediate Window (Ctrl+G), and enter:
To limit the results to zip files:
Call ListFiles("C:\Data", "*.zip")
To include files in subdirectories as well:
Call ListFiles("C:\Data", , True)
To show the files in a list box:
Public Function ListFiles(strPath As String, Optional strFileSpec As String, _ Optional bIncludeSubfolders As Boolean, Optional lst As ListBox) On Error GoTo Err_Handler 'Purpose: List the files in the path. 'Arguments: strPath = the path to search. ' strFileSpec = "*.*" unless you specify differently. ' bIncludeSubfolders: If True, returns results from subdirectories of strPath as well. ' lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window. ' The list box must have its Row Source Type property set to Value List. 'Method: FilDir() adds items to a collection, calling itself recursively for subfolders. Dim colDirList As New Collection Dim varItem As Variant Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders) 'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window. If lst Is Nothing Then For Each varItem In colDirList Debug.Print varItem Next Else For Each varItem In colDirList lst.AddItem varItem Next End If Exit_Handler: Exit Function Err_Handler: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Exit_Handler End Function Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _ bIncludeSubfolders As Boolean) 'Build up a list of files, and then add add to this list, any additional folders Dim strTemp As String Dim colFolders As New Collection Dim vFolderName As Variant 'Add the files to the folder. strFolder = TrailingSlash(strFolder) strTemp = Dir(strFolder & strFileSpec) Do While strTemp <> vbNullString colDirList.Add strFolder & strTemp strTemp = Dir Loop If bIncludeSubfolders Then 'Build collection of additional subfolders. strTemp = Dir(strFolder, vbDirectory) Do While strTemp <> vbNullString If (strTemp <> ".") And (strTemp <> "..") Then If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then colFolders.Add strTemp End If End If strTemp = Dir Loop 'Call function recursively for each subfolder. For Each vFolderName In colFolders Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True) Next vFolderName End If End Function Public Function TrailingSlash(varIn As Variant) As String If Len(varIn) > 0& Then If Right(varIn, 1&) = "\" Then TrailingSlash = varIn Else TrailingSlash = varIn & "\" End If End If End Function
ListFiles() is the main routine. It uses a collection to demonstrate how the file names can all be collected, and then output in different ways (list box, table, immediate window.)
FillDir() does the work of looping through the files in a folder that meet the file specification, and adding them to the collection. If we are to include the subfolders as well, the second part loops through all the files again to identify those that are directories. It ignores the "." and ".." entries, uses GetAttr() to identify the directories, and adds them to the colFolders collection. Then for each of the folders in this collection, the function calls itself again to handle the files in that folder. If that folder contains subfolders also, the function will continue to call itself recursively, to whatever depth is required.
The TrailingSlash() function just ensures that the folder names we are processing end with the slash character.
|Home||Index of tips||Top|