MS Access 2007--Copy folder information to file using click event (VBA)

Try this.

When the form opens, it populates the tables with the contents of a folder.
Notes:
You will have to modify the code to use your path to your specific folder
You will have to add a reference to the Windows Scripting Runtime library to your VBA editor.
Access--EEQ23466716ListFilesInFo.mdb

boaq2000,

Thank you for the quick response.  The code works great for Name and Modified Date of files contained within the specified folder.  What would I need to change if I wanted to copy the same information for folders contained within a specified folder--Is this possible?

This is about the same as boag2000's last suggestion but it's more generic and has a bit of error checking.

You must add the reference to Microsoft Scripting Runtime to your project. I think adding the reference and using early binding is better than using CreateObject() and late binding. But either will work for you.

Paste this code and kick it off with something like

ListFiles "C:\"

Public Sub ListFiles(szFolder As String)

    Dim fso         As FileSystemObject
    Dim fldrs       As Folders
    Dim fldr        As Folder
    
    Set fso = New FileSystemObject
    Set fldr = fso.GetFolder(szFolder)
    Set fldrs = fldr.SubFolders
    
    For Each fldr In fldrs
        GetFiles fldr
    Next
    
    Set fldr = Nothing
    Set fldrs = Nothing
    Set fso = Nothing
    
End Sub
 
Private Sub GetFiles(fldr As Folder)

    Dim fil         As File
    
    On Error Resume Next
    
    For Each fil In fldr.Files
    
        If (Err) Then
        
            With Err
                Debug.Print "** Error " & .Number & " " & .Description, fldr.Path
            End With
            
        Else
                
            With fil
                Debug.Print .Path, .DateCreated
            End With
            
        End If
    Next
    
    Set fil = Nothing
    
End Sub

Select allOpen in new window

This is an improvement of my last post, which included only one level of child folders.

This example will drill down and list your requested file info but only to eight levels. At that point it gets stuck in the loop and I don't know why it does that.

Hopefully someone else knows how to do it and can provide better advice. However, maybe you don't need to drill so deeply and this will work for you.

You may want to add some error handling to account for empty folders and "access denied" errors.

Run it with something like this to see the error:

ListFiles "C:\"

Run it with a starting folder that has fewer children and it should produce what you need, such as:

ListfFiles "C:\Program Files\Common Files"

As before, you must add the reference to Microsoft Scripting Runtime to your project.

Public Sub ListFiles(szFolder As String)

    Dim fso         As FileSystemObject
    Dim fldr        As Folder
    Dim fldrSub     As Folder
    Dim fil         As File
    
    Set fso = New FileSystemObject
    Set fldr = fso.GetFolder(szFolder)
    
    On Error Resume Next
    
    For Each fldrSub In fldr.SubFolders
    
        For Each fil In fldrSub.Files

            With fil
                Debug.Print .Path, .DateCreated
            End With
        
            ListFiles fldrSub.Path
            
        Next
    
    Next
    
    Set fil = Nothing
    Set fldr = Nothing
    Set fldrSub = Nothing
    Set fso = Nothing
    
End Sub

Select allOpen in new window

boaq2000,

Thank you for your help on this.  Your solution worked great, and it was extactly what I was looking for.

pancholefty,

I'll also take a look at your solution so that I can compare the two solutions.

Thank you to everyone for their input.

Thanks, But to be fair, give pancholefty's post a try.

My code is a bit "Rough", and I plan on tidying it up and making it a function.

I have no issue with the points being split if his solution ....
;-)

Jeff

Since I could't solve the shortcoming of my answer to this question, I asked my own question on to the VB group on EE.

dqmq provided the solution ("you've got your files and folders cross-wired")

Here is the edited version which will recurse to the end.

Public Sub ListFiles(szFolder As String)
'
' List all files in a directory tree
' szFolder = top folder e.g. "C:\"
'
    Dim fso         As FileSystemObject
    Dim fldr        As Folder
    Dim fldrSub     As Folder
    Dim fil         As File
    
    Set fso = New FileSystemObject
    Set fldr = fso.GetFolder(szFolder)
    
    On Error Resume Next
    
    For Each fil In fldr.Files
        Debug.Print fil.Path, fil.DateCreated
    Next
    
    For Each fldrSub In fldr.SubFolders
         ListFiles fldrSub.Path
    Next
    
    Set fil = Nothing
    Set fldr = Nothing
    Set fldrSub = Nothing
    Set fso = Nothing
    
End Sub

Select allOpen in new window