troubleshooting Question

ms/access programmatically get a list of fils and folders

Avatar of João serras-pereira
João serras-pereiraFlag for Portugal asked on
DatabasesMicrosoft Access
3 Comments1 Solution124 ViewsLast Modified:
I had this question after viewing ms/access get folder and file list.

On my ms/access app, I need to populate a table with filenames and their paths. It is doing so but, I cannot get to have the separate list of folders and empty folders that I need to populate the table.

My DB is attached and I the code I am using is:

ON THE IMMEDIATE window:

Call ListFiles("E:\01 preparacao\06 dgrh", , True)


ON THE MODULE:

Option Compare Database

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
            ' get the file name from the varItem
            strFileName = Replace(Right(varItem, Len(varItem) - InStrRev(varItem, "\")), "'", "''")
            strFilePath = Replace(Left(varItem, InStrRev(varItem, "\")), "'", "''")
            ' add 1 record to the Table of files
            strSetSQL = "INSERT INTO fileTable (fileName, filePath, fileType)"
            strSetSQL = strSetSQL & " VALUES ('" & strFileName & "', '" & strFilePath & "', 'file' ) "
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSetSQL
            DoCmd.SetWarnings True
        Next
    Else
        For Each varItem In colDirList
        lst.AddItem varItem
        Next
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    If Err = 52 Then
        'add the file to the table
        Err.Clear
        Resume Next
    End If
    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 strSetSQL As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
   
    On Error GoTo Err_Handler

    ' check if folder is empty
    If Nz(Dir(strFolder & "\*.*"), "") = "" Then
        'folder is empty
        'place codes to record the folder path and name
        strFilePath = Replace(Left(strFolder, InStrRev(strFolder, "\")), "'", "''")
        ' add 1 record to the Table of files
        FileName = "empty folder"
        strSetSQL = "INSERT INTO fileTable (fileName, filePath, fileType)"
        strSetSQL = strSetSQL & " VALUES ('" & strFileName & "', '" & strFilePath & "', 'folder' )    "
        Exit Function
    End If
    '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
   
   
Exit_Handler:
    Exit Function

   
Err_Handler:
    If Err = 52 Then
        'add the file to the table
        Err.Clear
        strSetSQL = "INSERT INTO fileTable (fileName, filePath, fileError) "
        strSetSQL = strSetSQL & " VALUES ('" & strFileName & "', '" & strFilePath & "', & 'Error 52') "
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSetSQL
        DoCmd.SetWarnings True
        Resume Next
    End If
    If Err = 53 Then
        'add the file to the table
        Err.Clear
        strSetSQL = "INSERT INTO fileTable (fileName, filePath, fileError) "
        strSetSQL = strSetSQL & " VALUES ('" & strFileName & "', '" & strFilePath & "', & 'Error 53') "
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSetSQL
        DoCmd.SetWarnings True
        Resume Next
    End If
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
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


can anyone help?

(the table is OK, but how to get just the folders is not...
Database3.accdb
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 3 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 3 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004