How to Export Folder and Subfolder Structure to Excel
Written by
Reviewed by
Last updated on June 5, 2023
This tutorial demonstrates how to export the folder and subfolder structure to Excel.
Import List of File Names and Folder Locations with Power Query
Using VBA to list the files in a folder is a great solution. However, Excel has introduced a way to list files using Power Query, where VBA is not required.
- In the Ribbon, go to Data > Get Data > From File > From Folder.
- Browse to the folder that contains the folders you want a list of, and then click Open.
- When you see the list of files, click Transform Data.
This opens the Power Query Editor.
- Using the Power Query Editor, remove columns you don’t want to import and/or merge columns together, depending on your needs and preferences.
To remove a column, select that column and in the Ribbon, go to Home > Remove Columns > Remove Columns.
- To remove more than one column at once, in the Ribbon, go to Home > Choose Columns > Choose Columns.
- Untick the columns you don’t need and click OK.
- In the Ribbon, go to Home > Close & Load to load the data into Excel.
- The data comes into Excel as a data table that is connected to the source folder. This means that, if you add another file to any of the folders selected and click Refresh, the Queries & Connections pane shows an updated number of files.
- Return to Power Query if you need to edit the information shown. In the Ribbon, go to Query > Edit.
- In the Ribbon, go to Home > Query > Manage Columns > Manage Columns, and then add a few more columns back into the query.
- Each column can be filtered. In the Extension column, for example, filter by .xlsx to show only Excel files.
Click Select All to remove checkmarks from all extensions, and then tick the .xlsx extension only.
- Click OK to apply the filter.
Tip: You could also filter by Attribute – in the case below, by size.
- If you applied any steps to the query that you no longer need, delete them from the Applied Steps list in the Query Settings pane on the right side of the screen.
- Select the step you wish to remove.
If you remove anything but the last step, you get 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.
Other tutorials that use Power Query
