Excel VBA用Evaluate读取SharePoint文件返回#VALUE!错误求助

• Evaluate不支持直接解析HTTPS格式的SharePoint路径：Evaluate函数仅能识别本地文件路径、映射的网络驱动器路径，或是WebDAV格式的SharePoint UNC路径，直接传入HTTPS URL会导致公式无法定位目标文件，返回#VALUE!错误。
• 路径格式不符合Excel外部引用规范：关闭文件的外部引用需要遵循[文件路径]工作表名!单元格地址的格式，而HTTPS URL不属于Excel认可的有效外部文件路径格式。
• 潜在路径拼接问题：如果Subfolder或Workbook单元格包含空格、特殊字符（如&、#等），直接拼接后会导致路径无效，进一步触发错误。

方案1：转换为SharePoint UNC路径（无需打开文件） #

将HTTPS路径转换为WebDAV格式的UNC路径，Excel可识别该格式读取关闭的SharePoint文件。修改后代码如下：

Sub PopulateTable()
    Dim i As Long
    Dim subFolder As String, workbookName As String
    Dim uncPath As String
    
    ' 替换为你的SharePoint站点UNC根路径
    Const SP_UNC_ROOT As String = "\\website-my.sharepoint.com@SSL\DavWWWRoot\Documents\"
    
    For i = 3 To 225
        workbookName = Cells(i, 1).Value
        subFolder = Cells(i, 2).Value
        
        ' 拼接完整UNC路径
        uncPath = SP_UNC_ROOT & subFolder & "\[" & workbookName & " Roombook.xlsx]Setup'!$C$3"
        
        ' 用IFERROR处理可能的读取错误
        Cells(i, 3).Formula = "=IFERROR(INDIRECT(""" & uncPath & """),""文件未找到/无权限"")"
        ' 可选：将公式转为静态值，避免后续路径变动导致错误
        ' Cells(i, 3).Value = Cells(i, 3).Value
    Next i
End Sub

注意：首次使用前需确保已访问过该SharePoint站点并完成身份验证，否则Excel无法读取路径。

方案2：使用GetObject后台读取数据（更可靠） #

若UNC路径方案仍有问题，推荐用GetObject后台打开目标工作簿，提取数据后关闭，兼容性更强且可添加错误处理：

Sub PopulateTable_WithGetObject()
    Dim i As Long
    Dim subFolder As String, workbookName As String
    Dim fullPath As String
    Dim targetWB As Workbook
    
    ' 替换为你的SharePoint站点HTTPS根路径
    Const SP_HTTPS_ROOT As String = "https://website-my.sharepoint.com/Documents/"
    
    On Error Resume Next ' 开启错误处理，避免单个文件出错中断循环
    For i = 3 To 225
        workbookName = Cells(i, 1).Value
        subFolder = Cells(i, 2).Value
        
        fullPath = SP_HTTPS_ROOT & subFolder & "/" & workbookName & " Roombook.xlsx"
        
        ' 后台打开目标工作簿
        Set targetWB = GetObject(fullPath)
        If Err.Number = 0 Then
            ' 提取目标单元格数据
            Cells(i, 3).Value = targetWB.Worksheets("Setup").Range("C3").Value
            ' 关闭工作簿，不保存
            targetWB.Close SaveChanges:=False
            Set targetWB = Nothing
        Else
            ' 错误提示
            Cells(i, 3).Value = "读取失败：" & Err.Description
            Err.Clear
        End If
    Next i
    On Error GoTo 0 ' 恢复默认错误处理
End Sub

优化说明 #

• 错误处理：避免单个文件路径错误、权限问题导致整个宏中断。
• 静态值存储：直接读取单元格值，比公式引用更稳定，不会因后续文件移动或权限变化触发错误。
• 后台操作：GetObject不会显示打开的工作簿窗口，不影响当前操作。

额外排查要点 #

• 检查Subfolder和Workbook列内容：确保无多余空格、特殊字符（如换行符、斜杠），这些会导致路径拼接无效。
• 验证权限：确认当前账号有权限访问所有目标SharePoint文件，可手动打开几个文件测试访问权限。

内容的提问来源于stack exchange，提问作者IEWorkSmart