By using File System Object, we can get list of all Files inside a folder. There can be two types of requirements here.
Either user wants all files in only one Folder or list of all files in All folders and its sub-folders. Following is the solution for both the requirement:

User wants to get the list of All files inside a folder
User wants to get the list of all files inside a folder as well as Sub-folders

Free Download – File Manager in Excel

i) VBA code to List all files within a Folder Only

Copy and Paste the below Code and this will list down the list of all the files inside the folder. This will list down all the files only in the specified folder. If there are other files which are there in some other Sub-folders.



Sub GetFilesInFolder(SourceFolderName As String)

'--- For Example:Folder Name= "D:\Folder Name\"

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    '--- This is for displaying, whereever you want can be configured

    r = 14
    For Each FileItem In SourceFolder.Files
        Cells(r, 2).Formula = r - 13
        Cells(r, 3).Formula = FileItem.Name
        Cells(r, 4).Formula = FileItem.Path
        Cells(r, 5).Formula = FileItem.Size
        Cells(r, 6).Formula = FileItem.Type
        Cells(r, 7).Formula = FileItem.DateLastModified
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"

        r = r + 1   ' next row number
    Next FileItem

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

ii) VBA code to List all files within a Folder and sub-folders as well

Copy and Paste the below Code and this will list down the list of all the files inside the folder as well as sub-folders. If there are other files which are there in some other Sub-folders then it will list down all files from each and Every Folders and Sub-folders.


Sub GetFilesInFolder(SourceFolderName As String, Subfolders As Boolean)

'--- For Example:Folder Name= "D:\Folder Name\" and Flag as Yes or No

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File
'Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    '--- This is for displaying, whereever you want can be configured

    r = 14
    For Each FileItem In SourceFolder.Files
        Cells(r, 2).Formula = r - 13
        Cells(r, 3).Formula = FileItem.Name
        Cells(r, 4).Formula = FileItem.Path
        Cells(r, 5).Formula = FileItem.Size
        Cells(r, 6).Formula = FileItem.Type
        Cells(r, 7).Formula = FileItem.DateLastModified
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"

        r = r + 1   ' next row number
    Next FileItem

    '--- This is the Function to go each and Every Folder and get the Files. This is a Nested-Function Calling.

    If Subfolders = True Then
        For Each SubFolder In SourceFolder.Subfolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

 

File Manager using Excel Macro in Excel Workbook

I have created one File Manager using the above Code. It basically fetches the list of Files from Folders and Sub-folders and list them. It fetches other details of the files as well like File Size, Last modified, path of the File, Type of the File and a hyperlink to open the file directly from the excel by clicking on that.
It looks something like below:

Excel File Manager

File Manager in Excel Workbook

Want to Download this File?

DOWNLOAD NOW