{"id":12085,"date":"2011-11-13T19:30:57","date_gmt":"2011-11-13T19:30:57","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=571"},"modified":"2022-08-17T19:21:24","modified_gmt":"2022-08-17T19:21:24","slug":"how-to-get-list-of-all-files-in-a-folder-and-sub-folders","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/11\/how-to-get-list-of-all-files-in-a-folder-and-sub-folders\/","title":{"rendered":"List All files from Folder and Sub-folders in Excel Workbook"},"content":{"rendered":"
[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.17.6″ custom_padding=”0px|0px|0px|0px|true|true” da_disable_devices=”off|off|off” global_colors_info=”{}” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on”][et_pb_row admin_label=”row” _builder_version=”4.17.6″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” custom_padding=”0px|0px|0px|0px|true|true” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”4.17.6″ _module_preset=”default” custom_padding=”0px|0px|0px|0px|false|false” global_colors_info=”{}”]In this article, we are going to learn about how to list the files from folders<\/span>. Here we will also learn how to list files from subfolders<\/span> as well. By using File System Object<\/span>, we can get the list of all Files inside a folder. An important thing to note here is that File System Object can list only files inside a folder. That means, there can be two scenarios here: User wants to get the list of All files inside a folder<\/a> <\/a><\/p>\n 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 that are there in some other Sub-folders.<\/p>\n <\/a><\/p>\n 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 that are there in some other Sub-folders then it will list down all files from each and Every Folder and Sub-folders.<\/p>\n <\/a>[\/et_pb_text][et_pb_blurb title=”You may want to read these articles too” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n [\/et_pb_blurb][et_pb_cta title=”File Manager using Excel Macro in Excel Workbook” button_url=”\/excel\/wp-content\/downloads\/File_Manager.xlsm” button_text=”Download File Manager” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]I have created one File Manager using the above Code. It basically fetches the list of Files from Folders and Sub-folders and lists them. It fetches other details of the files as well like File Size, Last modified, the path of the File, Type of the File, and a hyperlink to open the file directly from excel by clicking on that.
\nScenario 1: <\/span> User wants to list all files from the parent folder only. In this case, all the sub-folders inside the parent folder will be ignored.
\nScenario 2: <\/span> User wants to list of all files in parent folder and their sub-folders.
\nLet’s take a look in both the scenarios one by one.<\/p>\n
\nUser wants to get the list of all files inside a folder as well as Sub-folders<\/a>
\nFree Download – File Manager in Excel<\/a><\/p>\ni) VBA code to List all files within a Folder Only<\/h1>\n
\n\nSub GetFilesInFolder(SourceFolderName As String)\n\n'--- For Example:Folder Name= "D:\\Folder Name\\"\n\nDim FSO As Scripting.FileSystemObject\nDim SourceFolder As Scripting.folder, SubFolder As Scripting.folder\nDim FileItem As Scripting.File\n\n Set FSO = New Scripting.FileSystemObject\n Set SourceFolder = FSO.GetFolder(SourceFolderName)\n\n '--- This is for displaying, wherever you want can be configured\n\n r = 14\n For Each FileItem In SourceFolder.Files\n Cells(r, 2).Formula = r - 13\n Cells(r, 3).Formula = FileItem.Name\n Cells(r, 4).Formula = FileItem.Path\n Cells(r, 5).Formula = FileItem.Size\n Cells(r, 6).Formula = FileItem.Type\n Cells(r, 7).Formula = FileItem.DateLastModified\n Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"\n\n r = r + 1 ' next row number\n Next FileItem\n\n Set FileItem = Nothing\n Set SourceFolder = Nothing\n Set FSO = Nothing\nEnd Sub\n\n<\/code><\/pre>\n
ii) VBA code to List all files within a Folder and sub-folders as well<\/h1>\n
\nSub GetFilesInFolder(SourceFolderName As String, Subfolders As Boolean)\n\n'--- For Example:Folder Name= "D:\\Folder Name\\" and Flag as Yes or No\n\nDim FSO As Scripting.FileSystemObject\nDim SourceFolder As Scripting.folder, SubFolder As Scripting.folder\nDim FileItem As Scripting.File\n'Dim r As Long\n Set FSO = New Scripting.FileSystemObject\n Set SourceFolder = FSO.GetFolder(SourceFolderName)\n\n '--- This is for displaying, wherever you want can be configured\n\n r = 14\n For Each FileItem In SourceFolder.Files\n Cells(r, 2).Formula = r - 13\n Cells(r, 3).Formula = FileItem.Name\n Cells(r, 4).Formula = FileItem.Path\n Cells(r, 5).Formula = FileItem.Size\n Cells(r, 6).Formula = FileItem.Type\n Cells(r, 7).Formula = FileItem.DateLastModified\n Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"\n\n r = r + 1 ' next row number\n Next FileItem\n\n '--- This is the Function to go each and Every Folder and get the Files. This is a Nested-Function Calling.\n\n If Subfolders = True Then\n For Each SubFolder In SourceFolder.Subfolders\n ListFilesInFolder SubFolder.Path, True\n Next SubFolder\n End If\n\n Set FileItem = Nothing\n Set SourceFolder = Nothing\n Set FSO = Nothing\nEnd Sub\n<\/code><\/pre>\n
\n
\nIt looks something like the below:<\/p>\n