SharePoint列表视图名称与GUID获取：VBA XML请求报错求助

我正在编写Excel VBA宏，通过XML调用SharePoint站点的Web服务，获取指定列表的视图名称和GUID。之前获取所有列表名称及GUID的代码能正常运行，但适配成获取视图信息时遇到XML语法问题，运行后提示“Internal Server Error”。

原代码如下：

Sub GetViewGUIDs()
    Dim sURL As String
    Dim sEnv As String
    Dim RespText As String
    Dim xmlhtp As MSXML2.XMLHTTP60
    Dim xmlDoc As DOMDocument60
    Dim iLeng As Long
    Dim i As Long
    Dim Rowcnt As Long
    Dim ListGUID As String

    ListGUID = "{3ABAA8A3-C75D-4A57-BC7F-490443E381A2}"
    
    sURL = "https://yoursharepoint.com/sites/yoursite/_vti_bin/views.asmx"
    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    sEnv = sEnv & "  <soap:Body>"
    sEnv = sEnv & "    <GetViewCollectionResponse xmlns=""http://schemas.microsoft.com/sharepoint/soap/""><listName>" & ListGUID & "</listName>"
    sEnv = sEnv & "      <GetViewCollectionResult><xsd:schema>schema</xsd:schema>xml</GetViewCollectionResult>"
    sEnv = sEnv & "    </GetViewCollectionResponse>"
    sEnv = sEnv & "  </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"
    
    Set xmlhtp = New MSXML2.XMLHTTP60
    Set xmlDoc = New DOMDocument60
     
    With xmlhtp
        .Open "post", sURL, False
        .setRequestHeader "Host", "webservices.gama-system.com"
        .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        .setRequestHeader "soapAction", "http://schemas.microsoft.com/sharepoint/soap/GetViewCollection"
        .send sEnv
        xmlDoc.LoadXML .responseText
    End With
    
    RespText = xmlhtp.responseText

‘   more code here to parse the RespText and pick out the View Names and associated GUIDs
    
End Sub

问题根源 #

1. SOAP请求节点错误：请求体误用了响应节点GetViewCollectionResponse，正确的请求节点应为GetViewCollection
2. 冗余响应内容：请求中包含了<GetViewCollectionResult>...</GetViewCollectionResult>，这是服务返回响应时的结构，不属于请求参数
3. Host头不匹配：设置的Host值与目标SharePoint站点主机不一致，需改为站点实际主机（如yoursharepoint.com）

修正后的代码 #

Sub GetViewGUIDs()
    Dim sURL As String
    Dim sEnv As String
    Dim RespText As String
    Dim xmlhtp As MSXML2.XMLHTTP60
    Dim xmlDoc As DOMDocument60
    Dim ListGUID As String
    Dim viewNodes As IXMLDOMNodeList
    Dim viewNode As IXMLDOMNode
    
    ListGUID = "{3ABAA8A3-C75D-4A57-BC7F-490443E381A2}"
    sURL = "https://yoursharepoint.com/sites/yoursite/_vti_bin/views.asmx"
    
    ' 构建正确的SOAP请求格式
    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    sEnv = sEnv & "  <soap:Body>"
    sEnv = sEnv & "    <GetViewCollection xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">"
    sEnv = sEnv & "      <listName>" & ListGUID & "</listName>"
    sEnv = sEnv & "    </GetViewCollection>"
    sEnv = sEnv & "  </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"
    
    Set xmlhtp = New MSXML2.XMLHTTP60
    Set xmlDoc = New DOMDocument60
    xmlDoc.async = False
    xmlDoc.validateOnParse = False
    
    With xmlhtp
        .Open "POST", sURL, False
        .setRequestHeader "Host", "yoursharepoint.com" ' 修改为实际站点主机
        .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        .setRequestHeader "soapAction", "http://schemas.microsoft.com/sharepoint/soap/GetViewCollection"
        .send sEnv
        
        ' 加载响应XML
        If xmlDoc.LoadXML(.responseText) Then
            ' 设置命名空间
            xmlDoc.SetProperty "SelectionNamespaces", "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:sp='http://schemas.microsoft.com/sharepoint/soap/'"
            ' 获取视图节点集合
            Set viewNodes = xmlDoc.SelectNodes("//sp:GetViewCollectionResult/View")
            
            ' 遍历视图节点提取名称和GUID
            For Each viewNode In viewNodes
                Debug.Print "视图名称: " & viewNode.Attributes("DisplayName").Text
                Debug.Print "视图GUID: " & viewNode.Attributes("Name").Text
                Debug.Print "-------------------------"
            Next viewNode
        Else
            Debug.Print "XML解析错误: " & xmlDoc.parseError.reason
        End If
    End With
    
    Set xmlhtp = Nothing
    Set xmlDoc = Nothing
    Set viewNodes = Nothing
    Set viewNode = Nothing
End Sub

说明 #

• 修正后的SOAP请求严格遵循SharePoint Views Web服务的请求格式，仅传递必要的listName参数
• 添加了XML解析逻辑，通过命名空间定位视图节点，提取DisplayName（视图名称）和Name（视图GUID）属性
• 保留了错误处理逻辑，便于排查XML解析问题

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