Create the list of Files and Sub-folders in a Folder in Excel (Macro to list File Names)

This video is about a ' Macro ' for listing the files and subfolders in a particular folder. Here we have a folder called ' Reports and Drawings ' which is the subfolder of the folder called ' Smartcity Project ', in the E drive of my PC. And this folder contains 19 files of different formats. Like PDFs, AutoCAD drawings, word documents and Excel workbooks. For extracting the list of all these files in to the column E of this worksheet, Go to the ' Developer ' tab. Click on ' Visual basic '. Insert a ' Module '. I will name this ' Macro ' as ' ListFileNames '. (" Sub ListFileNames "). " Dim X As Object, XNC As Object " " Dim Y As Integer " Set X = CreateObject("Scripting.filesystemobject") Set XNC = X.GetFolder( Range (here we have to specify the cell containing the file path of the parent folder) I will be using this cell B2 for defining the file path of the parent folder. ("B2 ") .Value) [ Set XNC = X.GetFolder( Range ("B2").Value) ] Now a ' For loop ' for looping through every file in the folder. " For each Z in XNC. files " " Y = Y+1 " " Worksheets " Here we have to specify the ' Name of the worksheet ' where we want the filenames to be printed. I want the filename to be printed in the sheet called ' File Names '. " File Names " " Cells " Here we have to specify the starting cell of this list I need the list to start from cell E2 For that " Y +1,5. Value= Z.Name " " Next " Back to our worksheet. I will insert a ' Form Button ' here. I will assign the Macro ' ListFileNames ' to this button Will rename the button to ' Get File Names '. Now we have to specify the ' file path '. I will copy the file path from here. Paste it in the cell B2. Now when I click on this button called ' Get File Names '. The program will create the list of every file present in that particular folder ' Reports and Drawings '. Once again with a different folder. ' Copy ' the file path. ' Paste ' it into the cell B2. Click on the button called ' Get File Names ' to extract the list. We can also extract the file path with a simple modification to our code. Go to VBA editor. I will copy this statement. Modify the column offset to 6. Z.Name should be Z.Path Back to our worksheet. Before executing the program I will delete this list. Once again Click on the button ' Get File Names '. And this time we have extracted the File paths too. Once again with a different folder. ' Copy ' the file path. ' Paste ' it into the cell B2. And we have the filenames along with File paths. Now about a Macro for listing the sub folders of a particular folder. I will copy this code. Paste it into a new Module. We will rename the Macro as ' List Folder Names ' ' XNC . Files ' should be modified to ' XNC.SubFolders '. Here the sheetnames should be folder names. This statement is not necessary and I will delete it. Back to our worksheet I will insert a ' Form Button ' here. I will assign the Macro ' ListFolderNames ' to this Button I will rename this button to ' Get Folder Names '. Now we have to specify the path of the parent folder. You can see here, this folder called ' Youtube ' contains 62 sub folders. For extracting the ' list ' of all these sub folders. I will copy the path from the address bar. Paste it into the cell B2. Execute the Macro. And here we have the list of all those folders present in the folder called Youtube in the E drive of my PC. I hope you will find this video useful. For feedback and discussion, Please make use of the comment section. And those who are visiting the channel for the first time ' Subscribe ' to the channel .Press the ' Bell ' icon, so that you won't miss anything. Thanks for watching. Have great day.

Channel: Ajay Anand

Views: 29,926

Keywords: Macro for Creating list of file names, macro to list files, macro to list subfolders, macro to list folders, Macro to create list of folders, macro to create list of files, macro to create list of filepaths, excel macro for file paths, macro for file paths, excel vba, excel macro, excel tutorial, Excel VBA, VBA Excel, Excel Macro, MS Excel, Microsoft Excel, Excel Podcast

Id: Y-GKsF4qUd4

Length: 8min 2sec (482 seconds)

Published: Sun Jun 02 2019