VBA写入SharePoint中XML文件出现路径语法错误的解决办法

我编写了一段VBA脚本，用于将Excel工作簿中的数据插入XML文件。待编辑的XML文件通过文件选择器选取：

Public strFile As String
    
Sub SetPath()

' Declare filedialog picker
Set fd = Application.FileDialog(msoFileDialogFilePicker)

' Define attributes for the filepicker to only allow a single xml file
With fd
    .Filters.Clear
    .Filters.Add "XML files", "*.xml", 1
    .Title = "Choose file"
    .AllowMultiSelect = False
    .InitialFileName = ActiveWorkbook.Path & "\"
    
    ' If a file is chosen save the path and name
    If .Show = True Then
    
        strFile = .SelectedItems(1)
    
    Else
        
        'MsgBox "Error"
    
    End If

End With

End Sub

随后打开、编辑并保存该文件：

Sub WriteXML()
   
Call SetPath

' Open the xml-file
Dim xmlFile As String
xmlFile = strFile
     
Set xmlWriter = CreateObject("Microsoft.XMLDOM")
xmlWriter.async = False
xmlWriter.Load xmlFile
    
' long codeblock for editing the file
    
xmlWriter.Save xmlFile
Set xmlWriter = Nothing
    
End Sub

这段代码在私人电脑上运行正常，但在公司电脑执行时出现错误：“文件名、目录名或卷标语法不正确”。

问题根源是公司电脑使用SharePoint，文件选择器返回的是HTTPS路径：
https://company-my.sharepoint.com/personal/mat_company_com/Documents/Desktop/myTemplates/Templates/file.xml
而非私人电脑的本地路径：
C:\Users\Mat\Documents\file.xml

方法1：临时文件中转（最通用） #

Microsoft.XMLDOM不支持直接读写HTTPS路径的文件，我们可以先把SharePoint上的文件下载到本地临时目录，编辑完成后再上传回原位置：

Public strFile As String
Private tempPath As String

Sub SetPath()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear
        .Filters.Add "XML files", "*.xml", 1
        .Title = "Choose file"
        .AllowMultiSelect = False
        .InitialFileName = ActiveWorkbook.Path & "\"
        
        If .Show = True Then
            strFile = .SelectedItems(1)
        Else
            MsgBox "未选择文件"
            Exit Sub
        End If
    End With
End Sub

Sub WriteXML()
    Call SetPath
    If strFile = "" Then Exit Sub
    
    ' 生成本地临时文件路径
    tempPath = Environ("TEMP") & "\temp_edit.xml"
    
    ' 1. 从SharePoint下载文件到本地
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    xmlHttp.Open "GET", strFile, False
    xmlHttp.Send
    
    If xmlHttp.Status = 200 Then
        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Dim ts As Object
        Set ts = fso.CreateTextFile(tempPath, True, True)
        ts.Write xmlHttp.responseText
        ts.Close
    Else
        MsgBox "下载文件失败，错误代码：" & xmlHttp.Status
        Exit Sub
    End If
    
    ' 2. 加载并编辑临时XML文件
    Dim xmlWriter As Object
    Set xmlWriter = CreateObject("Microsoft.XMLDOM")
    xmlWriter.async = False
    xmlWriter.preserveWhiteSpace = True ' 保留原文件格式
    If xmlWriter.Load(tempPath) Then
        ' 此处放入你的XML编辑代码
        ' 示例：xmlWriter.SelectSingleNode("//TargetNode").Text = "新内容"
        
        ' 保存编辑后的临时文件
        xmlWriter.Save tempPath
    Else
        MsgBox "加载临时XML失败：" & xmlWriter.parseError.reason
        Exit Sub
    End If
    
    ' 3. 将修改后的文件上传回SharePoint
    xmlHttp.Open "PUT", strFile, False
    xmlHttp.setRequestHeader "Content-Type", "application/xml"
    xmlHttp.send fso.OpenTextFile(tempPath).ReadAll
    
    If xmlHttp.Status = 200 Or xmlHttp.Status = 201 Then
        MsgBox "文件已成功保存到SharePoint"
    Else
        MsgBox "上传文件失败，错误代码：" & xmlHttp.Status
    End If
    
    ' 清理临时文件
    fso.DeleteFile tempPath, True
    
    ' 释放对象
    Set xmlWriter = Nothing
    Set xmlHttp = Nothing
    Set fso = Nothing
End Sub

方法2：使用Office原生XML对象 #

如果你的Office版本支持，可以直接用Office.OpenXMLPackage处理SharePoint路径（需引用对应版本的Microsoft Office Object Library）：

Sub WriteXML_OfficeObject()
    Call SetPath
    If strFile = "" Then Exit Sub
    
    Dim xmlPackage As Object
    Set xmlPackage = CreateObject("Office.OpenXMLPackage")
    xmlPackage.Open strFile
    
    ' 读取XML内容
    Dim xmlDoc As Object
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    xmlDoc.async = False
    xmlDoc.Load xmlPackage.Part(1).GetStream
    
    ' 此处放入你的XML编辑代码
    ' 示例：xmlDoc.SelectSingleNode("//TargetNode").Text = "新内容"
    
    ' 保存修改回SharePoint
    xmlPackage.Part(1).SetStream xmlDoc.XML
    xmlPackage.Save
    
    Set xmlDoc = Nothing
    Set xmlPackage = Nothing
    MsgBox "文件保存成功"
End Sub

方法3：映射SharePoint库为网络驱动器 #

如果公司允许，可将SharePoint文档库映射为本地网络驱动器（如Z:\），这样文件选择器会返回类似Z:\Desktop\myTemplates\Templates\file.xml的路径，Microsoft.XMLDOM就能直接读写：

1. 打开Windows文件资源管理器，点击「此电脑」→「映射网络驱动器」
2. 在「文件夹」输入框粘贴SharePoint库路径（格式示例：\\company-my.sharepoint.com@SSL\DavWWWRoot\personal\mat_company_com\Documents）
3. 勾选「登录时重新连接」，点击完成

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