This tutorial will demonstrate how to export the folder and subfolder structure to Excel.
Import a list of File Names and Folder Locations with Power Query
Using VBA to list the files in a folder is a great solution in Excel however now Excel has introduced a way to do this using Power Query where VBA is not required.
- In the Ribbon, select Data > Get Data > From Folder.
- Browse to the folder that contains the files and sub-folders you wish to obtain a list of, and click Open.
- When the list of files is shown, select Transform Data.
The Power Query editor will open.
- Using the Power Query editor, we can remove columns to import into Excel as well as merging columns together if we wish.
To remove the selected column, In the Ribbon, select Home > Remove Columns > Remove Columns.
- To remove more than one column at a time, in the Ribbon, select Home > Choose Columns > Choose Columns.
- Deselect the columns not required, and click OK.
- In the Ribbon, select Home > Close & Load to load the data into Excel.
- The data is returned to Excel as a data table which is connected to the folder it is referring to. This means that if we add another file to any of the folders selected, and then click Refresh, the number of files will be updated.
- To return to Power Query in order to edit the information shown, in the Ribbon, select Query > Edit.
- In the Ribbon, select Home > Query > Manage Columns > Manage Columns and then add a few more columns back into the query.
- A the top of each column is the ability to filter. In the Extension column, we are going to filter by .xlsx to only show Excel Files.
Click Select All to deselect all the Extensions, and then select the .xlsx extension only.
- Click OK to apply the filter.
We can also filter by Attribute – in the case below, by size.
- If we have applied any steps to the query that we wish to remove, we can delete them from the Applied Steps list in the Query Settings on the right hand side of the screen.
- Select the step you wish to remove.
If you remove anything but the last step, you will receive a warning. However, you may delete the steps in the order you applied them without getting a warning.
- Once you have adjusted your data, click Close and Load to load the data into Excel.