Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
In general, if you have a choice between Power Query and VBA, go with Power Query as it tends to be more flexible and more robust than VBA.
You can download the example file at the button at the bottom of the post.
We need the ability to select the folder that we want a list of files from and we will use and input cell to do that. We’ll give that input cell a range name and we will amend the power query to use that name.
The input cell for the folder is cell A2 and it is named rngFolder – see image below.
We will create a query that lists Excel files from a particular folder. We will then edit that query to use the folder in the range name.
File Listing Query
- Click the Data ribbon tab. Click the Get Data drop-down and then click From File and then From folder.
- We then need to navigate to the folder we want, so click the Browse option and find and select the folder and click OK.
- The list of files will be displayed. Click the Transform Data button at the bottom of the dialog.
- Right click the Extension column and choose Transform and then choose lowercase. This make sure all of the extensions listed are in lowercase. This makes it easier when we filter in our next step.
- Click the filter drop-down on the Extension column and choose Text Filters and then choose Begins With.
- If you want list all the files you can omit this step. In the box on the right of “begins with” type .xls and click OK.
- You can remove any of the columns that you don’t need by right clicking and choose remove. Click and Close & Load on the Home ribbon. The output table is shown below.
- Currently this query only works for one folder. We can edit the query and make it more flexible by using the range name folder which we can easily change.
- Open the query. And click the Advanced Editor icon. I named the query File_list. We need to make the following changesa) Click after the Let statement then press Enter then press Tabb) Copy the line below and paste in the Query
temp = Excel.CurrentWorkbook() {[Name="rngFolder"]}[Content]{0}[Column1],
don’t miss the comma on the end
c) Change the next line to
Source = Folder.Files(temp),
Just enter the temp name between the brackets – that’s it.
- Click Done.
Now changing the yellow input cell allows you to list files from a different folder.
Change the input cell and Refresh the query to see the files.
Please note: I reserve the right to delete comments that are offensive or off-topic.