SharePoint Online下VBA邮件合并文件引用（Open语句）报错咨询

VBA的Open语句无法直接读取SharePoint Online上的文件——SharePoint在线路径是HTTP/HTTPS格式，而Open仅支持本地文件系统路径（包括映射网络驱动器，但在线模式下映射常不稳定）。你当前用strCurDir + "\settings.txt"生成的路径如果是SharePoint在线路径，必然导致Open语句报错。

1. 替换文件读取方式，适配SharePath路径 #

重写ImportVariable函数，用Word的文档对象打开文本文件读取内容，绕过Open语句的限制：

Private Function ImportVariable(strFile As String) As String
    Dim tempDoc As Document
    On Error GoTo ErrorHandler
    
    ' 以只读模式打开文本文件
    Set tempDoc = Documents.Open(FileName:=strFile, ReadOnly:=True, Format:=wdOpenFormatText)
    ' 读取第一行内容并移除末尾段落标记
    ImportVariable = Trim(tempDoc.Paragraphs(1).Range.Text)
    ImportVariable = Left(ImportVariable, Len(ImportVariable) - 1)
    
    ' 关闭临时文档，不保存
    tempDoc.Close SaveChanges:=wdDoNotSaveChanges
    Set tempDoc = Nothing
    Exit Function
    
ErrorHandler:
    MsgBox "读取文件失败: " & strFile & vbCrLf & Err.Description, vbExclamation
    ImportVariable = ""
    If Not tempDoc Is Nothing Then
        tempDoc.Close SaveChanges:=wdDoNotSaveChanges
        Set tempDoc = Nothing
    End If
End Function

2. 修正路径与驱动器逻辑 #

移除无效的ChDrive "S"语句，直接使用模板的完整路径拼接文件，避免依赖CurDir：
在Document_Open过程中修改路径相关代码：

' 移除ChDrive "S"
' 获取模板所在的完整路径
Dim templatePath As String
templatePath = ActiveDocument.AttachedTemplate.Path

''EDIT BELOW 2 LINES ONLY
strFileName = ImportVariable(templatePath & "\settings.txt")
strTabName = "NEW DB"
''EDIT ABOVE 2 LINES ONLY

strLongName = templatePath & "\" & strFileName

3. 修复数据源连接字符串 #

原连接字符串末尾截断，且Data Source应使用完整路径strLongName，而非仅文件名：

Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & strLongName & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Password="""";"

Private Sub Document_Close()
    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub

Private Sub Document_Open()
    Dim strFileName As String
    Dim strTabName As String
    Dim templatePath As String
    Dim strLongName As String
    Dim strSQL As String
    
    ' 获取模板所在的SharePoint路径
    templatePath = ActiveDocument.AttachedTemplate.Path
    
    ''EDIT BELOW 2 LINES ONLY
    strFileName = ImportVariable(templatePath & "\settings.txt")
    strTabName = "NEW DB"
    ''EDIT ABOVE 2 LINES ONLY
    
    strLongName = templatePath & "\" & strFileName

    strSQL = "SELECT * FROM `'" & strTabName & "$'`"
    
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        strLongName, ReadOnly:=False, LinkToSource:=True, _
        Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & strLongName & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Password="""";" _
        , SQLStatement:=strSQL, _
        SubType:=wdMergeSubTypeAccess
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub

Private Function ImportVariable(strFile As String) As String
    Dim tempDoc As Document
    On Error GoTo ErrorHandler
    
    Set tempDoc = Documents.Open(FileName:=strFile, ReadOnly:=True, Format:=wdOpenFormatText)
    ImportVariable = Trim(tempDoc.Paragraphs(1).Range.Text)
    ImportVariable = Left(ImportVariable, Len(ImportVariable) - 1)
    
    tempDoc.Close SaveChanges:=wdDoNotSaveChanges
    Set tempDoc = Nothing
    Exit Function
    
ErrorHandler:
    MsgBox "读取文件失败: " & strFile & vbCrLf & Err.Description, vbExclamation
    ImportVariable = ""
    If Not tempDoc Is Nothing Then
        tempDoc.Close SaveChanges:=wdDoNotSaveChanges
        Set tempDoc = Nothing
    End If
End Function

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