One method would be to use MS Query. From your workbook, go to
Data>Import External Data>New Database Query. Then follow what the
wizard. You can set it so that the files automatically requeries every time you open the workbook.
If you need to populate just specific cells, add a new worksheet to the workbook, drop the data into the new worksheet, and use links to that worksheet.
Another method would be to use an ADO recordset in an OnOpen macro to pull the data and populate the cells
