For loop to pull data from specific cells from multiple workbooks

Question

I’m trying to 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 FSO As Object
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

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 file name variable and repeated the printing process making a list of all values in cell A1 from every workbook in the folder.

Dim FSO As Object
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.

Answer 1

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