Dynamically Reference cells from other files

Posted by Marvin on August 20, 2001 7:25 AM

Hello here is my question...
I have this formula to access data from another Excel file...
=path\[filename.xls]worksheetname'cellreference
I have many similar files in one directory that have the same worksheetname and data location (cell). I would like to have a drop down box in another cell that would provide a list of filenames to select from in another cell. When the drop down list is changed then the value from that field would update the formula above and its value. Unfortunatly I cannot get this to work and am not sure whether I am doing something wrong or if it can't be done in this context. I would appreciate any help I can get.

Thanks
Marvin

Posted by Damon Ostrander on August 20, 2001 10:24 AM

Hi Marvin,

In order to reference a workbook or any objects within a workbook in Excel, the workbook must be open in Excel. For each file you will need to use the Open method to open the file, then access it and read and/or write cells, then Save (if you have changed it) and Close the file.

Damon