Creating a reference to a Workbook with a fixed location but with a changeable file name..

[elaiwa]

I'm trying take a reference from some workbooks (e.g. WB1.xls,WB2.xls,WB3.xls,...etc)
They are all placed in one folder but there filename is subject to change,

I need to get the result of Cells A1 from WB1.xls+A1 from WB2.xls + A1 from WB3...etc
to shown on AA1 in another workbook(e.g. Collected.XLS)

Is this possible knowing that workbooks filenames are changeable?
Moreover Can the value of A1 from any new workbook added to the same folder be added automatically??...

Can the formula typed in AA1 from Collected.xls be draged down?(So as to show the sum of Cells B1 and C1.....etc) from each workbook?

 

[elaiwa]

Dear Owen,

I've just checked the workbook ,it looks great where as I have a problem with it,

Whenever I use the Read Workbook names button it just clears the list of workbooks(not replacing with the correct workbooks) knowing that I changed the path in cell B1 and that the path contains the workbooks I need to read,do you know what could be the problem
(The path I used is C:\Documents and Settings\Pc\My Documents\Temp Data),

By the way it works if I changes each workbook link manually,

Thanks,

regards,

 

[MorganO]

You need to have a trailing \ at the end of the path. So your path will be:
C:\Documents and Settings\Pc\My Documents\Temp Data\

Sorry, I didn't check for this in the code!

Hope it's helpful!

Owen

 

[elaiwa]

Wow,
I never thought this could be the problem,

It worked,
Thank you so much your code is working perfectly now,

Thanks for your time and concern,

Take care

 

[Hai]

Hi Owen,

Would appreciate very much if you could send me the link to the sample worksheet that you've created for elaiwa. It's just the thing I've been looking for

Thank you so much,

Hai.