Get sub-folder name & filename within sub-folders

[Robie]

Hi

Is there a way to get a list of the names of all sub-folders and filenames of the document within the sub-folder? There is only *one* document under each sub-folder. For example,

C:\The Library\Documents\Functional Specifications. Under 'Functional Specifications' we have 100s of sub-folders *each* containing a *single file*. What I need to do is get the name of the sub-folder and the filename within it and compare to existing reports.

Thanks.
Robie

[HansV]

Here is a macro you can use as starting point:

Code: Select all

Sub LoopSubfolders()
    ' Main folder path
    Const strParent = "C:\The Library\Documents\Functional Specifications"
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim fil As Object
    Dim strPath As String
    Dim strFile As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strParent)
    For Each sfl In fld.SubFolders
        strPath = sfl.Path
        For Each fil In sfl.Files
            strFile = fil.Name
            ' Do something with the path and filename, for example
            Debug.Print strPath, strFile
        Next fil
    Next sfl
End Sub

[Robie]

Here is a macro you can use as starting point:

Code: Select all

Sub LoopSubfolders()
    ' Main folder path
    Const strParent = "C:\The Library\Documents\Functional Specifications"
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim fil As Object
    Dim strPath As String
    Dim strFile As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strParent)
    For Each sfl In fld.SubFolders
        strPath = sfl.Path
        For Each fil In sfl.Files
            strFile = fil.Name
            ' Do something with the path and filename, for example
            Debug.Print strPath, strFile
        Next fil
    Next sfl
End Sub

Wow Hans. Thanks for the quick response.
That will do me very well.