Hello John I hope you are doing well. In this issue, you will learn a quick and easy way to get a list of all files in a folder (and subfolders) and import the list into Excel. To do this, we’ll be using the Excel Power Query add-in. This is one of the best things about Excel, and a tool we use when we automate our clients’ Excel processes. In addition to the file name, you'll get the following information:File extensionDate accessedDate modifiedDate createdFolder PathHere’s how it works.
Creating the List Open Excel and start a new workbook.Click the Data tab in the ribbon. Click Get Data, then From File, then From Folder. |
|
| Click the name of the folder, then click Open. |
|
| A preview pane pops up, showing a list of some of the files in the folder. Click the Load button. |
|
| This places a table in your Excel workbook with a list of files in the folder you selected. This includes files in the subfolders. |
|
| Save the workbook and you're done! Refreshing the List The best thing about this list is that it is updateable. As you add or remove files from the folders, the list will update. Here’s how you refresh the list. Click the Data tab on the ribbon. In the Queries & Connections group, click Refresh All, then Refresh All. (Shortcut: ALT A R A) |
|
| Interested in learning more about Power Query? Contact us for more information about our 1-on-1 and group training. We also handle automating Excel processes using Power Query and VBA (Visual Basic for Applications). Warmly, |
|
| Since 2001, Phoenix Computer Consultants LLC has provided Excel and other Microsoft Office training to thousands of business professionals. |
|
|
