Loop through SubFolder of a Subfolder

[Kiwiman]

I Have been playing around with this now to try and loop through all subfolders (and their subfolders etc) of a directory, looking for csv files, so I can import the filenames into a table.

This is the first time I have played around with File Scripting objects. I found mot of this code on this site, and added the some code in order to load the necessary info into my table.

This code works for the subfolders of the named directory, but ignores any subfolders of the subfolder. If you get my meaning.

I'm not sure how on the syntax on how to search all subfolders \ subfolders of a chosen directory. There are alot of posts regarding looping through directories, but I can't quite get the job done. Any help would be appreciated.

Sub printFolders()
    Dim FS As New FileSystemObject
    Dim objFld As Folder
    Dim objFile As File
    Dim strSQL As String
    Dim intItem As Integer
    
    Dim ctlList As Access.ListBox
    Dim lngCount As Long
    Dim strFolder As String
    Dim strFile As String

    Dim FSfolder As Folder
    Dim Subfolder As Folder
    Dim strName As String
    Dim i As Integer
    
    Set FSfolder = FS.GetFolder("C:\Documents and Settings\LLockett.CERIDIANHR\My Documents\Work\AP Cleanse\")
    Debug.Print FSfolder
    For Each objFile In FSfolder.Files
      
            Select Case Right(objFile.Name, 3)
                Case Is = "CSV"
                    strSQL1 = "INSERT INTO tblFiles ( FIleName,FilePath) SELECT '" & objFile.Name & "','" & FSfolder & "'"
                    DoCmd.RunSQL strSQL1
                Case Else
            End Select
            lngCount = lngCount + 1
            Next
      Stop
      
    i = 0
    For Each Subfolder In FSfolder.SubFolders
        DoEvents
            strName = FSfolder & "\" & Subfolder.Name & "\"
            Debug.Print strName
            Set objFld = FS.GetFolder(strName)
            
            Debug.Print objFld
            For Each objFile In objFld.Files
            Debug.Print objFile
            
            Select Case Right(objFile.Name, 3)
                Case Is = "CSV"
                    strSQL1 = "INSERT INTO tblFiles ( FIleName,FilePath) SELECT '" & objFile.Name & "','" & strName & "'"
                    DoCmd.RunSQL strSQL1
                Case Else
            End Select
            lngCount = lngCount + 1
            Next
'do your rs stuff here
        'Debug.Print Subfolder.Name
        i = i + 1
    Next Subfolder
    Set FSfolder = Nothing

End Sub

 

[PeregrinTook]

Hey Kiwiman,

How about this? Credit largely due to Erlandsen Data Consulting for the original ListFilesInFolder function which was written for excel, I just modified it a bit to suit your purposes...

HTH
J

Sub ExecuteListFiles()

    Call ListFilesInFolder("C:\Documents and Settings\LLockett.CERIDIANHR\My Documents\Work\AP Cleanse\", True)

End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
Dim FSO As New FileSystemObject, SourceFolder As Folder, Subfolder As Folder, FileItem As File
Dim lngCount As Long, strSQL As String
    
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
        Select Case Right(FileItem.Name, 3)
            Case "CSV"
                strSQL = "INSERT INTO tblFiles (FIleName,FilePath) SELECT '" & FileItem.Name & "','" & SourceFolder.Path & "'"
                DoCmd.RunSQL strSQL
        End Select
        lngCount = lngCount + 1
    Next FileItem
    If IncludeSubfolders Then
        For Each Subfolder In SourceFolder.SubFolders
            ListFilesInFolder Subfolder.Path, True
        Next Subfolder
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub

 

[Steve R.]

I am not proposing what I have below as a solution, though it would work to generate a list of all files. In another post I noted that an MS-DOS batch file would seem to be the appropriate solution. In seeing this post, I thought I would experiment a bit. Essentially this solution works by using an MS-DOS command to dump all file names (in this example "*.doc" files) into a text file that Access would then parse.

Private Sub Command0_Click()

    Dim RetVal
    RetVal = Shell("cmd.exe /c dir *.doc c:\ /s /b > Temp.txt", 0)

End Sub

Microsoft has a bit of advise here. Note that even Microsoft would not recommend the solution I have above. Microsoft recommends using the VBA equivalent commands. This was done as a learning exercise.

Here is a listing of the switches for the DIR command.

As a final note, you need to use "cmd.exe" and not "command.com" since "command.com" does not recognize long file names.

 

[Kiwiman]

Thanks Pippen

Your solution worked a treat. Thansk a lot. Ortaias - thanks for the reply, but I never tried your solution as the first post worked. Cheers

 

[wildwassit]

Hi,

This looks great but I think I have a referencing issue as Access 07 is not recognising Dim FSO As FileSystemObject. Says its an undefined user type.

Any ideas?

Thanks

 

[wildwassit]

Found Microsoft Scripting Runtime was the missing reference