List folders and subfolders with Microsoft Scripting Runtime

 2005-01-28    Files & Folders    4    228

Microsoft Scripting Runtime is included in these products: Windows98, Windows2000, IE5, and Office2000. The macro examples below assumes that your VBA project has added a reference to the Microsoft Scripting Runtime library. You can do this from within the VBE by selecting the menu Tools, References... and selecting the Microsoft Scripting Runtime library.

Sub TestListFolders()
    Application.ScreenUpdating = False
    Workbooks.Add ' create a new workbook for the folder list
    ' add headers
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "Folder Path:"
    Range("B3").Formula = "Folder Name:"
    Range("C3").Formula = "Size:"
    Range("D3").Formula = "Subfolders:"
    Range("E3").Formula = "Files:"
    Range("F3").Formula = "Short Name:"
    Range("G3").Formula = "Short Path:"
    Range("A3:G3").Font.Bold = True
    ListFolders "C:\FolderName", True
    Application.ScreenUpdating = True
End Sub

Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the folders in SourceFolder
' example: ListFolders "C:\FolderName", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    ' display folder properties
    r = Range("A65536").End(xlUp).Row + 1
    Cells(r, 1).Formula = SourceFolder.Path
    Cells(r, 2).Formula = SourceFolder.Name
    Cells(r, 3).Formula = SourceFolder.Size
    Cells(r, 4).Formula = SourceFolder.SubFolders.Count
    Cells(r, 5).Formula = SourceFolder.Files.Count
    Cells(r, 6).Formula = SourceFolder.ShortName
    Cells(r, 7).Formula = SourceFolder.ShortPath
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFolders SubFolder.Path, True
        Next SubFolder
        Set SubFolder = Nothing
    End If
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2011-03-16 12:36:19 (GMT)

You can find a script that list files here:
List files in a folder with Microsoft Scripting Runtime

Johnny | 2011-03-16 10:11:14 (GMT)

A wonderfull script. It works great. But is it also possible to filter files, p.e. to find all files *.jpg or *.xls?

OPE | 2009-04-19 18:35:03 (GMT)

You have to change C:\Foldername to a valid folder path, e.g. (in Windows Vista):
ListFolders "C:\Users", True

hjald8 | 2009-04-19 17:44:05 (GMT)

Very good code.
I tried to change "C:\Foldername" to af string without any succes.
Dim Folder as string
Folder = "R:\Foldername"
Listfolders Folder, True
How do I get it to work?