Hi:
I found this info concerning your question which you might find helful:
Excel provides a very powerful function - INDIRECT. It just lacks the functionality to access closed workbooks. The following is a compilation of common alternative solutions presented in the Excel newsgroups:
Laurent Longre has developed the free add-in MOREFUNC.XLL which includes the function INDIRECT.EXT
You can use it in the same way as INDIRECT. e.g.:
=INDIRECT.EXT(��C:temp[book1.xls]sheet!�A1�)
Note that in this formula you also specify the path/directory information
If you use INDIRECT.EXT with an open workbook it behaves the same way as INDIRECT does
There�re some limitations to this function:
It does not work reliable on all computers. So you have to try it in your individual environment.
INDIRECT.EXT can only return a single cell reference from a closed workbook. So you can�t use it for example as second parameter in a VLOOKUP function:
=VLOOKUP("search_text",INDIRECT.EXT("'C:temp[book1.xls]sheet!'A1:B20"),2,0)
won�t work.
INDIRECT.EXT does not work with defined names within closed workbooks.
If you have to access several closed workbooks your spreadsheet can become slow while re-calculating.
Use SQL.REQUEST:
The usage is described here.
This function is relatively slow and the data has to be organized in a database like structure (that is a single area with field names as top row).
Note: Microsoft does not support this addin anymore.
Use Harlan Grove�s PULL function:
Code can be found here.
The function creates separate Excel application instances to �pull� data from closed workbooks.
This function is more robust, can deal with non-database like layouts and can also deal with cell ranges.
Example usage:
=VLOOKUP("search_text",PULL("'C:temp[book1.xls]sheet!'A1:B20"),2,0)
All these solutions have one common drawback: They’re quite slow. So use them carefully and don’t use them with large cell ranges.
plettieri
