How to Export Folder and Subfolder Structure to Excel

This tutorial will demonstrate how to export the folder and subfolder structure to Excel.

 

ImportFolder Intro

 

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.

  1. In the Ribbon, select Data > Get Data > From Folder.

 

ImportFolder Ribbon

 

  1. Browse to the folder that contains the files and sub-folders you wish to obtain a list of, and click Open.

 

ImportFolder Open

 

  1. When the list of files is shown, select Transform Data.

 

ImportFolder Transform

 

The Power Query editor will open.

 

ImportFolder PowerQuery

 

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

 

ImportFolder RemoveColumns

 

  1. To remove more than one column at a time, in the Ribbon, select Home > Choose Columns > Choose Columns.

 

ImportFolder Choose Columns

 

  1. Deselect the columns not required, and click OK.

 

ImportFolder Load

 

  1. In the Ribbon, select Home > Close & Load to load the data into Excel.

 

ImportFolder Loaded data

 

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

 

ImportFolder Refresh

 

  1. To return to Power Query in order to edit the information shown, in the Ribbon, select Query > Edit.

 

ImportFolder PowerQuery Edit

 

  1. In the Ribbon, select Home > Query > Manage Columns > Manage Columns and then add a few more columns back into the query.

 

ImportFolder-PowerQuery AddColumns

 

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

 

ImportFolder SelectExcelOnly

 

  1. Click OK to apply the filter.

 

ImportFolder FilterXL

 

We can also filter by Attribute – in the case below, by size.

 

ImportFolder SelectSizeOnly

 

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

 

ImportFolder ShowSteps

 

  1. Select the step you wish to remove.

 

ImportFolder RemoveStep

 

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.

 

ImportFolder Warning

 

  1. Once you have adjusted your data, click Close and Load to load the data into Excel.