I'm trying to make a For loop that can pull data from specific cells but from mulitple different work books

FYI, I have no idea what I’m doing in VBA. I’m not new to coding in general but my knowlage isn't that great and to add to it VBA is completely new to me.

So what I’m trying to do is create a script that can access several different workbooks in a specific folder and pull data from a pre-determined cell on each workbook. All of the workbooks are test logs so their format is identical and so are the cell ID I am using.

I know you can use the formula ='[File_Name]Sheet1'!R1C1 to pull the date out of cell A1 on that specific file. And I found a basic script that could retrieve the names of all files in a specific folder.

Dim Folder As Object
Dim File As Object
Dim i As Integer

Set FSO = CreateObject("Scripting.FileSystemObject")

Set Folder = FSO.GetFolder("C:\Users\Callum\Documents\Test Logs")

For Each File In Folder.Files

    Sheets("Data").Select

    Cells(i + 1, 1) = File.Name

    i = i + 1

Next File

End Sub

So the plan was to combine the two so that the script would retrieve the file name, save it as a variable, and then print the equation using the file name variable. This should have in theory printed the value from the requested cell on the source workbook, found the next file in the folder, set it to the fine name variable and repeated the printing process making a list of all values in cell A1 from every workbook in the folder.

Dim Folder As Object
Dim File As Object
Dim Name As String
Dim i As Integer

Set FSO = CreateObject("Scripting.FileSystemObject")

Set Folder = FSO.GetFolder("C:\Users\Callum\Documents\Test Logs")

For Each File In Folder.Files

    Name = File.Name

    Sheets("Data").Select

    Cells(i + 1, 1) =  "='[Name]Sheet1'!R4C2"

    i = i + 1

Next File

End Sub

Instead, it didn’t appear that the variable holding the file name was registered when printing the formula so every time you ran the script it would ask you to manually select the file you wanted to use.

Perhaps the way I'm going about it is compleatly wrong and there is a much simpler method but again I'm a novice among novices when it comes to VBA so any assistance would be appreciated.

Issues:

• VBA doesn't do variable expansion. A common approach is to concatenate.
• You need the full filepath, not just the filename.
• Consider using a different variable name than Name since that shadows the Name statement. But you don't even need the variable here.
• It's good to be explicit that you're writing a R1C1-style formula here:

Dim path As String
path = "C:\Users\Callum\Documents\Test Logs\" ' note the ending backslash

Set Folder = FSO.GetFolder(path)
For Each File In Folder.Files
    Dim f As String
    f = "='" & path & "[" & File.Name & "]Sheet1'!R4C2"

    ThisWorkbook.Worksheets("Data").Cells(i + 1, 1).FormulaR1C1 = f
    i = i + 1
Next File