how to select variables in same cells of multiple workbooks in Excel

I am running an exp开发者_StackOverflow社区eriment that is 8 weeks long and consists of about 100 subjects. There are variables from each week that I would like to select, but each subject has his own workbook. Is there a way to select the variables (which are the same cells in each workbook) all into one workbook? or can I do this with macro?

You can pull values from a cell in another workbook with a formula like this (assuming the other workbook is in the same folder):

=[Book1.xlsx]Sheet1!A1

Or you can specify the full path to another workbook like this (note where the single quotes go -- particularly the one after the sheet name):

='C:\Users\Brandon\Desktop\test\[Book1.xlsx]Sheet1'!$A$1

I think Excel automatically changes it to the full path style when you put it in the first way I mentioned. You can of course use the value in equations as well:

=6 + 5 * [Book1.xlsx]Sheet1!A1