Excel VBA - Evaluate function returning #VALUE! error

Question

I am writing a VBA Macro in Excel to populate a "Summary" Workbook with data from 200+ closed Workbooks. I wrote the following, but get a #VALUE! error in every cell that is populated.

Workbook and subfolder names have their own columns in the "Summary" Workbook.

Sub PopulateTable()

    Dim i As Long, j As Long
    Dim Subfolder As Variant, Workbook As Variant
    
    For i = 3 To 225
        For j = 3 To 3
            Workbook = Cells(i, 1)
            Subfolder = Cells(i, 2)
            Cells(i, j) = Evaluate("https://website-my.sharepoint.com/Documents/" & Subfolder & "/[" & Workbook & " Roombook.xlsx]Setup'!$C$3")
        Next j
    Next i
    
End Sub

What's causing the #VALUE! error? Is there a better or working solution to this? Thanks in advance.

I have tried changing data types, that did not work.

Answer 1

There are several options to get value from closed workbook (local storage). I am not sure if it works for Sharepoint shared files.

Local workbook file is D:\temp\List.xlsx which is not opened in Excel. Let's try to get the value of cell A1 w/o open it.

---

Option1：

Cell address must be in RC style.

MsgBox ExecuteExcel4Macro("'D:\temp\[List.xlsx]Sheet1'!R1C1")

---

Option2:

Set a formula on any cell, it will pull data from closed workbook. Then you can manipulate the data with your vba code.

=INDEX('D:\Temp\[List.xlsx]PG'!$A$1,1,1)