During a recent Excel training course, someone asked if they could get Excel to look inside a folder on their computer, grab the names and sizes of the files in that folder and load that information into an Excel workbook.
This was something that they wanted to automate because they had to do it every week…with 31 files!
Traditionally this would have to be done using VBA, Excel’s built-in programming language. But now, using Power Query, anyone can do it. No programming knowledge is required!
Overview of Process (TL;DR)
In Excel, select Data > Get Data > From File > From Folder. Navigate to the Folder and click Open. A list of the files in that folder is displayed. Click Transform Data to open the Query Editor.
Amongst the columns that are displayed is the name of every file in that folder. To display the the size of each file, click the double headed arrow to the right of the Attributes heading. This reveals a list of file attributes. Untick Select All, tick Size and click OK.
Video
For those who want more details than a couple of paragraphs, this week’s YouTube video covers the entire process including a demo of the above plus
- How to remove the extension from the filename
- How to display the file size in KB
- How to apply a filter to select specific files
Download a copy of the file(s) used in this video: https://share.zight.com/2NuQY4e0