Automated filename capture

[jayron]

I have a folder full of a number of different Excel files that are utilized by another spreadsheet for summary purposes. As I add new Excel files, I would like to have a way to automatically gather the data in the summary spreadsheet without having to manually go and enter the new filenames.

Is there a way to automatically populate data into the summary file from, for example, all files in a given location?

Thanks,

J

[Leith Ross]

Hello Jayron,

Here is macro to list all the files of specific type on the Summary Sheet. You change the variables (in blue) to select where on the worksheet you want the list to start, the directory to list, and the file type. If you want to list all files use "*.*"

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Right Click on any Sheet's tab
4. Left Click on View Code in the pop up menu
5. Press the keys ALT+I to activate the Insert menu
6. Press M to insert a Standard Module
7. Paste the code by pressing the keys CTRL+V
8. Make any custom changes you need to the macro
9. Save the Macro by pressing the keys CTRL+S

Sincerely,
Leith Ross