excel - How to reference files, each in a different folder?

Question

I have 100 Excel files, and each file is located on diffrent folder in the drive.

For eg.

file 1 = C:DATA_DIRTEST_12018_11_26.0001Excel.xlsx  
file 2 = C:DATA_DIRTEST_12018_11_26.0002Excel.xlsx 
file 3 = C:DATA_DIRTEST_12018_11_26.0003Excel.xlsx
.
.
.
file 100 = C:DATA_DIRTEST_12018_11_26.0100Excel.xlsx

In each Excel file, the data is located at cell (1,1).

I want to take the data from every Excel file and save it in one Excel file in one column.

The code I have, was good enough for small amount of data,

a=xlsread('K:RepeatabilityC1X2018-11-07.0001Radial_Accuracy.xlsx'); 

b=xlsread('K:RepeatabilityC1X2018-11-07.0002Radial_Accuracy.xlsx'); 

N=[a(1:1,:) ; b(1:1,:)] 
xlswrite('K:Repeatability_TestNew.xlsx',N);

In this case and in future, I'll have a bigger amount of data. I don't want to specify each folder location.

Answer 1

In case you can have the workbooks open

You can use the INDIRECT function.
In column A, you autofill numbers 1 till 100.
In column B, you put the following formula, which you copy down :
=INDIRECT("'C:DATA_DIRTEST_1[2018_11_26.0"&TEXT(A1,"000")&"Excel.xlsx]Sheet1'!$A$1") .

I used Sheet1 as the name of the worksheet, but this you have to change with the actual name. And I assumed that with cell(1,1) you meant cell A1.

As a next step, I would select column B, and copy/paste-as-values the results in column C, to get rid of the 100 references to other excel files, which will slow down opening or re-calculating the workbook every time.

Two sources for the solution : https://exceljet.net/formula/dynamic-workbook-reference and https://www.contextures.com/xlFunctions05.html if you want to do some further reading or fine-tune/trouble-shoot the formula.

But, as mentioned above, the INDIRECT function does only work if the other workbook is open. If you close the source workbooks, and you do a F9 in the workbook with all the results (or re-open again), you will get the #REF! again, so therefore my recommendation to copy/paste-as-values once you have the results.
See also here : Referencing value in a closed Excel workbook using INDIRECT?

Workaround for closed workbooks

The solution basically creates the direct cell references to the other workbooks as a text string, which you then copy/paste-as-values, and then you either click in each cell, so the formula gets "activated" and will get your result from the other workbook, or you use the text-to-columns functionality of Excel and do this automatically.

Some details. Again assuming that you have numbers 1 till 100 in Column A. Put this in B1 : ="='C:DATA_DIRTEST_1[2018_11_26.0"&TEXT(A1,"000")&"Excel.xlsx]Sheet1'!$A$1" and copy down.

Now you have to select this range (or the entire column B), and then paste-as-values in column C. If you now click in cell C1 and tap Enter, you will see that the formula gets executed and you see the actual result (i.e. the value in the other workbook). If you have many cells, this is quite cumbersome, so just select Column C, go to Data > [Data Tools] Text To Columns, click Finish, and all values from the different workbooks will appear.