How To Get A List Of File Names From A Folder And All Subfolders

2017-12-25

Excel is awesome! But with so many people using Excel in large companies, the number of spreadsheets used can become quite massive. Keeping track of all these can become a job in itself.

I worked at a large insurance company and we were in the midst of a project to take inventory of all the spreadsheets used.

We created a VBA procedure that would take a folder path and spit out all the file names in that folder. Each department was responsible for using this to take inventory of all their files. Fortunately for me, my department used only a few folders and subfolders for our work otherwise I would have tried to modify the code to loop through all subfolders in a folder.

We were using Excel 2010 and installing the Power Query add-in was not an option due to IT security lock down. With Power Query this would have been a much less time consuming task and with no coding needed.

Create a From Folder Query

From any workbook that you want to create a file inventory in, you can create a From Folder query. Go to the Data tab in the ribbon and select Get Data from the Get & Transform section. Then choose the From File option in the menu and then the From Folder option in the submenu.

Select the Parent Folder to Query

Add the folder path of the parent folder which you want to query. You can copy and paste this from a windows file explorer address bar or you can use the Browse option to select the folder.

Edit the From Folder Query

A preview will show and you can check the output to make sure it’s the correct folder. Press the Edit button to edit the query. Editing is an optional step if you want more information on the files. Otherwise you can Load the query without editing.

Remove the Content Column

If all you’re looking for is the list of file names from the folders, then you don’t need this column. This column can be used to import data from multiple files in multiple folders.

To remove the Content column, right click on the column heading and select Remove from the menu.

I also like to move the Folder Path column over to the left most column. You can do this by dragging and dropping the column.

Expand the Attribute Column for More Information

Expanding the Attributes column will allow us to see more detailed information about the files. Otherwise we will only see the Folder Path, Name, Extension, Date accessed, Date modified and Date created which may well be all you want to see.

  1. Left click on the filter icon on the Attributes column.
  2. Select or unselect the different file attributes you want to see in the query results.
  3. Uncheck Use original column name as prefix if you don’t want your new attribute columns to be prefixed (ie. Attributes.Kind)
  4. Press the OK button.

You’ll now see the extra columns you chose in the query editor preview.

Close and Load the Query

From the Home tab, press the Close & Load button.

Query Results List All Files

The query will load and then you’ll have a table with information on all the files from your chosen folder and subsequent subfolders. You can then filter this table to look at particular folders or file types, or sort on dates to find the most recent version of a file.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

8 Comments

  1. Ben

    My excel version, which fairly new, does have the “Get folders” option

    • John

      You need Excel 2016 or Office 365 for power query to be baked in, otherwise it won’t be there. There is an add-in available for some prior versions of Excel.

  2. Kimberley

    Thank you
    this is much better than recalling my old and rusty DOS!

  3. Marcela

    Thank you so much Jonh for the clear explanations, I tried it! It’s going to be very helpful! I was supposed to work in the vba code the company has, but it’s a big puzzle (many people have worked on it). I’m gonna propose this tool!

    Could we add a column with the link to access directlly to the file in the list created?

    • John

      You could use the HYPERLINK Excel function after loading the list to a table in Excel.

  4. Klay

    Is there a way to make this list updated automatically? So that when one of the files from the list will show the latest date?

    • John

      Go to the Data tab and press Refresh.

  5. Iva

    Thank you very much!

    How can I automatically get the hyperlinks to the files when I have them listed?

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This