Locate Files of Predefined Extension, in Predefined Location & List in Spreadsheet

Hi All,

I require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.

Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly.

NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.

I don't know if the above can be done - but I welcome your suggestions and example files if you can.

Cheers

Hello R_S_6,

Place this macro in the your Workbook_Open() event module. When the workbook opens it will ask you if you want to update the file list. Only new files will be added to the list. The list is set for "Sheet2" starting in cell A2. You will need to change the folder and the file extension variables as well in the macro. These are all marked in red. Make these changes before you save the macro. This code works with Excel 2000 and up.

Please Login or Register  to view this content.

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross