求助：VBA连接SharePoint Excel文件执行查询时报错问题

问题场景 #

本地路径的VBA代码可正常从Excel文件查询数据，但使用SharePoint UNC路径时，连接能建立，但执行SELECT查询时报错，错误出现在Set result1 = conn.Execute(sqlQuery)语句。

本地可运行代码 #

Sub ConnectToSharePointExcel()
    ' 设置Excel文件路径
    Dim sharePointPath As String
    Dim conn As ADODB.Connection
    Dim result1 As Object
    
    Set conn = CreateObject("ADODB.Connection")
    sharePointPath = "C:\Users\" & Environ("username") & "\orgname\Base Tables\Master Data.xlsx"

    ' 创建连接字符串    
    conn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & sharePointPath & _
            ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
        conn.Open

    ' 注：原代码存在重复调用conn.Open的冗余问题，此处保留原样
    conn.Open connString
    
    sqlQuery = "SELECT * FROM [Master_Ocean_New$]"
    Set result1 = conn.Execute(sqlQuery)

    ' 处理数据（此处可自定义逻辑）

    ' 关闭连接
    conn.Close
End Sub

报错的SharePoint路径代码 #

Sub ConnectToSharePointExcel()
    ' 设置SharePoint上的Excel文件路径
    Dim sharePointPath As String
    Dim conn As ADODB.Connection
    Dim result1 As Object
    
    Set conn = CreateObject("ADODB.Connection")

    sharePointPath = "\\sharepoint\sites\orgname\Shared%20Documents\Base%20Tables\Master%20Data.xlsx"

    ' 创建连接字符串
    Dim connString As String                 
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "WSS;HDR=YES;IMEX=2;" & _
                 "RetrieveIds=Yes;" & _
                 "DATABASE=" & sharePointPath & ";"
    conn.open
    
    Dim sqlQuery As String
    sqlQuery = "SELECT * FROM [Master_Ocean_New$]"
    Set result1 = conn.Execute(sqlQuery)

    ' 处理数据（此处可自定义逻辑）

    ' 关闭连接
    conn.Close
End Sub

错误原因 #

1. URL编码不兼容：SharePoint路径中的%20是URL编码的空格，ACE驱动无法识别，需替换为实际空格
2. 连接参数错误：WSS参数用于连接SharePoint列表，而非存储在SharePoint上的Excel文件，应使用Excel文件标准连接参数
3. 冗余连接调用：原代码存在重复执行conn.Open的问题，可能引发意外错误

修正后的代码 #

Sub ConnectToSharePointExcel()
    Dim spPath As String
    Dim conn As ADODB.Connection
    Dim rs As Object
    Dim sqlQuery As String

    ' 初始化连接对象
    Set conn = New ADODB.Connection

    ' 1. 替换URL编码空格为实际空格，使用正确的SharePoint UNC路径
    spPath = "\\sharepoint\sites\orgname\Shared Documents\Base Tables\Master Data.xlsx"

    ' 2. 采用和本地Excel一致的连接字符串格式，无需WSS相关配置
    conn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & spPath & ";" & _
        "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2;"";"

    ' 打开连接
    conn.Open

    ' 执行查询
    sqlQuery = "SELECT * FROM [Master_Ocean_New$]"
    Set rs = conn.Execute(sqlQuery)

    ' 示例：将查询结果输出到当前工作表A1开始的区域
    If Not rs.EOF Then
        ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rs
    End If

    ' 清理资源
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

关键说明 #

• 路径处理：确保SharePoint UNC路径使用实际空格，不要保留%20编码
• 连接字符串：存储在SharePoint上的Excel文件，连接逻辑和本地文件完全一致，无需额外的SharePoint专用参数
• 资源清理：显式关闭记录集和连接，释放对象避免内存泄漏

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