Pulling data-points from different workbooks

[elpaco313]

I have 57 data-points (per file) across 120 separate Excel files. Each point is in the same spot in each workbook. I have been searching everywhere for the past 3 hours on how to achieve this. I keep coming across the "INDIRECT" function, but not sure if that is the correct solution (and I don't know how to use it).

I already have a list of the file names compiled and in a sheet now, as well as the folder path.

 

[cerfani]

if the files are closed, indirect won't work.

let's say paths are in A and filenames in B... and you want to write the data in C... the data point is A1

Sub GetDataPoints()
    Dim r, endRow As Long
    endRow = Cells(Rows.Count, 1).End(xlUp).Row

   [COLOR=#008000] 'file path must look like this... C:\something\ ...make sure it has a trailing slash
    'file name must include extension
[/COLOR]
    For r = 2 To endRow
        Cells(r, 3) = "='" & Cells(r, 1) & "[" & Cells(r, 2) & "]'!A1"
    Next r
End Sub

you cant use indirect so you have to use vba to build the dynamic ranges