SharePoint上Excel宏ADO连接报错，寻求同文件数据访问解决方案

我做了个带宏的Excel文件，宏用ADO连接自身工作表处理大量数据，本地运行正常，但移到SharePoint网络驱动器后，执行cn.Open strCon报错。文件路径是SharePoint的URL格式：

https://mycompany.sharepoint.com/sites/directoryPath/file.xlsm

怀疑问题出在URL格式或网络凭证，同时想知道有没有更简便的方法直接访问已打开文件里的其他工作表。当前代码如下：

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
  & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";;"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon

解决方案 #

1. 修复ADO连接（适配SharePoint环境） #

ACE OLEDB驱动不支持直接解析SharePoint的HTTPS URL，得换成本地路径：

• 用OneDrive同步路径：如果已经通过OneDrive同步了SharePoint站点，文件的本地路径一般是C:\Users\你的用户名\OneDrive - 你的公司名\sites\站点名\目录路径\file.xlsm，此时ThisWorkbook.FullName会自动返回这个本地路径（前提是同步完成）。
• 映射网络驱动器：右键SharePoint文档库，选择「映射到网络驱动器」，得到类似Z:\directoryPath\file.xlsm的路径，替换代码里的strFile。

修改后的代码示例：

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strFile As String
Dim strCon As String

' 获取本地同步路径（OneDrive同步后）或手动替换为映射驱动器路径
strFile = ThisWorkbook.FullName
' 若自动获取的还是URL，手动替换（示例）
strFile = Replace(strFile, "https://mycompany.sharepoint.com/sites/directoryPath", _
                  "C:\Users\你的用户名\OneDrive - 你的公司名\sites\directoryPath")

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
  & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strCon

2. 直接访问已打开文件的工作表（无需ADO） #

如果只是处理当前文件内的数据，直接用Excel对象模型更简单，完全绕开路径问题：

示例1：读取工作表数据到数组处理

Dim ws As Worksheet
Dim dataArr As Variant

' 指定要操作的工作表
Set ws = ThisWorkbook.Worksheets("Sheet1")
' 获取工作表已使用区域的所有数据到数组（效率极高）
dataArr = ws.UsedRange.Value

' 遍历数组处理数据
Dim i As Long, j As Long
For i = LBound(dataArr, 1) To UBound(dataArr, 1)
    For j = LBound(dataArr, 2) To UBound(dataArr, 2)
        ' 这里写你的数据处理逻辑，比如Debug.Print dataArr(i,j)
    Next j
Next i

示例2：用SQL查询当前工作簿（仍需本地路径）

如果一定要用SQL语法，也可以直接连接当前工作簿，但还是得确保路径是本地的：

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
        ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

' 示例SQL：查询Sheet1中A列值大于100的行
strSQL = "SELECT * FROM [Sheet1$] WHERE A > 100"
Set rs = cn.Execute(strSQL)

' 将查询结果输出到Sheet2的A1单元格开始位置
ThisWorkbook.Worksheets("Sheet2").Range("A1").CopyFromRecordset rs

' 关闭连接和记录集
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

内容的提问来源于stack exchange，提问作者Paul K.