Code for querying a list of filenames within a network directory

[ascendantP]

Hello,
After several days of searching, I haven't been able to find any threads related to this. I'm making a search form that queries a pdf library table. Once a search query is entered, the user is able to open the files from a results form. Currently, I am entering the filenames from the network directory manually into the pdf library table.

For example, I manually enter the following information into the tblPDFLibrary table that contains the fields:
Date added, Filename, File Path, Series, Class, Title.

I was wondering if there's a way to have Access query all the filenames in the directory for me. In other words, if the directory contains the filenames: AccountsPDF, InventoryPDF, CustomerPDF. Can I have Access query all the filenames within the directory and automatically add all the file names (AccountsPDF, InventoryPDF, CustomerPDF) to the Filename field within the tblPDFLibrary table? If I am able to do this, I can code the other fields to populate information because the filename contains all the other field information (except for file path but I can program it insert the UNC path).

Thanks!

 

[pr2-eugin]

Allen Brown has something like this.. List files recursively

 

[TJPoorman]

This code should get you on the right track. There is no way to directly query the file system, but this code will run through each file and you can write the information to a table.

Place this in a public module

Public Function RecursiveDir(colFiles As Collection, strFolder As String, strFileSpec As String, bIncludeSubfolders As Boolean)
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

On Error Resume Next

'Add files in strFolder matching strFileSpec to colFiles
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
    colFiles.Add strFolder & strTemp
    strTemp = Dir
Loop

If bIncludeSubfolders Then
    'Fill colFolders with list of subdirectories of strFolder
    strTemp = Dir(strFolder, vbDirectory)
    Do While strTemp <> vbNullString
        If (strTemp <> ".") And (strTemp <> "..") Then
            If InStr(1, strTemp, "?") = 0 Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                End If
            End If
        End If
        strTemp = Dir
    Loop

    'Call RecursiveDir for each subfolder in colFolders
    For Each vFolderName In colFolders
        Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
    Next vFolderName
End If
End Function

Then call it with something like this
You can limit the files that are returned by changing the RecursiveDir line from *.* to *.PDF

Dim objFolder As String
Dim colFiles As New Collection
Dim vFile As Variant

objFolder = "C:\Test" ' Set this to your folder file path
If Right(objFolder, 1) <> "\" Then objFolder = objFolder & "\" ' Make sure the path always ends with a backslash.

RecursiveDir colFiles, objFolder, "*.*", True

For Each vFile In colFiles
    Debug.Print vFile.name
Next vFile

 

[ascendantP]

Wow, thank you so much guys. Looks like this is exactly what I needed. I love this site!

 

[pr2-eugin]

Most welcome ! Glad to help !