selecting values form multiple excel files into an excel worksheet

[martha]

I have created a number of excel files, which have the same name but a
different number. For e.g. book1.xls, book2.xls, book3.xls..... and so on. I
need to retrieve information from a particular cell(the address is fixed)
from all these files into another new worksheet. The number of excel
files(i.e., book1.xls,book2.xls, book3.xls.....etc.) may vary, i.e., there
can be 50 or 60 or even 100 files. I first need to find out the number of
files, and then retrieve the cell information from all these files and store
it in the new file. Also, if changes are made in any one of the files, then
the change should reflect in the new worksheet.

 

[Kaak]

You should loop through the files

For each file you should:

Open a file
Get the value you want
Close the file
And put the value in the master sheet

voila