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.
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.
