List all Files in a Folder in Excel Using Power Query
January 05, 2018 - by Bill Jelen
Today’s question: Marcia needs to get a list of all of the tax invoice PDF files from a folder in to an Excel spreadsheet. This is easy to do if you are using Excel 2016 on a Windows PC using the new Get & Transform Data tools.
If you have Excel 2010 for Windows or Excel 2013 for Windows, you will have to download the free Power Query Add-in from Microsoft. Go to your favorite search engine and type “Download Power Query” to find the current link. (Microsoft loves to change URLs every quarter and my awesome web guy hates when our links are out of date, so I am not even going to try to put a link here.)
The video above will show you the complete steps, but here is the overview:
- Start from a blank worksheet
- Data, Get Data, From File, From Folder
- Browse to the folder
- Click Edit instead of Load
- Open the filter dropdown on file type and remove anything that is not a PDF
- Open the filter on folder and remove any garbage subfolders
- Keep only File Name and Folder – right-click each column heading and choose Remove
- Drag Folder heading to the left of File heading. This allows the merge to work.
- Select both columns. Click on one heading. Shift-Click on the other heading.
- Choose Add Column, Merge Columns, Type a new Name for the Column. Click OK.
- Right-Click the heading for the new column and Remove Other Columns
- Home, Close & Load
- The amazing part… you can refresh the query later. Click the Refresh Icon in the Queries & Connections panel.
While Power Query is amazingly powerful, this is one of my favorite tasks. I frequently want to run a VBA Macro on each file in a folder. Getting a list of all the PDFs in a folder is a good starting point.
To learn about Power Query, I recommend this book by Ken Puls and Miguel Escobar.