VBA什么也不返回

Question

我第一次尝试使用excel宏，我编写了解析web服务响应的代码，我想更新excel中单个单元格中的标记值。 以下是我的XML的摘录(一个巨大的web服务响应)

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   <SOAP-ENV:Header xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"/>
   <soap:Body>
      <bm:getTransactionResponse xmlns:bm="http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1">
         <bm:status>
            <bm:success>true</bm:success>
            <bm:message>Wed Apr 06 09:04:32 UTC 2016 - Successfully processed API for test1</bm:message>
         </bm:status>
         <bm:transaction>
            <bm:category>data</bm:category>
            <bm:action>add</bm:action>
            <bm:id>1111</bm:id>
            <bm:process_var_name>xvgfdr</bm:process_var_name>
            <bm:buyer_company_name>test1</bm:buyer_company_name>
            <bm:supplier_company_name>test1</bm:supplier_company_name>
            <bm:step_var_name>waitingForInternalApproval</bm:step_var_name>
            <bm:last_document_number>2</bm:last_document_number>
            <bm:date_added>2016-04-04 12:14:57</bm:date_added>
            <bm:date_modified>2016-04-06 09:04:18</bm:date_modified>
            <bm:data_xml>
               <bm:transaction bm:bs_id="11111" bm:buyer_company_name="test1" bm:buyer_user_name="someone" bm:currency_pref="GBP" bm:data_type="0" bm:document_name="Transaction" bm:document_number="1" bm:document_var_name="transaction" bm:process_var_name="XXX_1" bm:supplier_company_name="test1">
                  <bm:_document_number>1</bm:_document_number>
                  <bm:createdBy_t>SomeOne</bm:createdBy_t>
                  <bm:_price_book_var_name>_default</bm:_price_book_var_name>
                  <bm:createdDate_t>2016-04-04 00:00:00</bm:createdDate_t>
                  <bm:currency_t>INR</bm:currency_t>
                  <bm:_customer_t_first_name/>
                  <bm:_customer_t_last_name/>
                  <bm:_customer_t_company_name>Test Account</bm:_customer_t_company_name>

我正在尝试获取标记<bm:_customer_t_company_name>的值。

下面是我一直在使用的代码。

Sub Button1_Click()

                'Set and instantiate our working objects
                    Dim Req As Object
                    Dim sEnv As String
                    Dim Resp As New MSXML2.DOMDocument60
                    Set Req = CreateObject("MSXML2.XMLHTTP")
                    Set Resp = CreateObject("MSXML2.DOMDocument.6.0")                   
                    With Req

                    .Open "Post", "https://XXXX.com/", False                    
                    Dim Pwd As String
                    Pwd = Range("D8").Value


                    Dim QuoteId As String
                    QuoteId = Range("D9").Value

                    ' SOAP envelope for submission to the Web Service
                     sEnv = sEnv & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"">"
                     sEnv = sEnv & "  <soapenv:Header>"
                     sEnv = sEnv & "  <wsse:Security xmlns:wsse=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"" xmlns:wsu=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"">"
                     sEnv = sEnv & "  <wsse:UsernameToken wsu:Id=""UsernameToken-2"">"
                     sEnv = sEnv & "  <wsse:Username>" & Range("D7").Value & "</wsse:Username>"
                     sEnv = sEnv & "  <wsse:Password Type=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText"">" & Pwd & "</wsse:Password>"
                     sEnv = sEnv & "  </wsse:UsernameToken>"
                     sEnv = sEnv & "  </wsse:Security>"
                     sEnv = sEnv & "  </soapenv:Header>"
                     sEnv = sEnv & "  <soapenv:Body>"
                     sEnv = sEnv & "   <bm:getTransaction>"
                     sEnv = sEnv & "    <bm:transaction>"
                     sEnv = sEnv & "    <bm:id>" & Range("D9").Value & "</bm:id>"
                     sEnv = sEnv & "   </bm:transaction>"
                     sEnv = sEnv & "   </bm:getTransaction>"
                     sEnv = sEnv & "  </soapenv:Body>"
                     sEnv = sEnv & "</soapenv:Envelope>"                    
                    ' Send SOAP Request
        .send (sEnv)       

        Resp.LoadXML Req.responseText

        End With
        If Resp Is Nothing Then
            MsgBox "No XML"
        End If
        With Resp

        .setProperty "SelectionNamespaces", "xmlns:bm=""http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""
        Dim strName As String
        If .SelectSingleNode("//bm:_customer_t_company_name") Is Nothing Then
            MsgBox "No Node"
        End If
        strName = .SelectSingleNode("//bm:_customer_t_company_name").Text
        MsgBox strName
    End With

  'clean up code
    Set Req = Nothing
    Set Resp = Nothing
Range("A1").Value = "DONE"
End Sub

.SelectSingleNode("//soap:Body")运行良好。当我测试我的代码时，.SelectSingleNode("//bm:_customer_t_company_name")总是什么都不返回。也是如此 .SelectSingleNode("//bm:getTransactionResponse")，什么也不返回。 你能告诉我我做错了什么吗？

下面是完整XML结构在这里输入图像描述的屏幕截图

Answer 1

你展示的代码没有什么问题。您应该检查web服务实际上正在返回的XML，以确保它具有您要查找的标记。

我将您的示例简化为下面的VBA Sub，它运行时没有错误。

Sub Test()
    Dim xml
    Set xml = CreateObject("MSXML2.DOMDocument.6.0")
    xml.LoadXML "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/""> <SOAP-ENV:Header xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/""/> <soap:Body> <bm:getTransactionResponse xmlns:bm=""http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1""> <bm:status> <bm:success>true</bm:success> <bm:message>Wed Apr 06 09:04:32 UTC 2016 - Successfully processed API for test1</bm:message> </bm:status> <bm:transaction> <bm:category>data</bm:category> <bm:action>add</bm:action> <bm:id>1111</bm:id> <bm:process_var_name>xvgfdr</bm:process_var_name> <bm:buyer_company_name>test1</bm:buyer_company_name> <bm:supplier_company_name>test1</bm:supplier_company_name> <bm:step_var_name>waitingForInternalApproval</bm:step_var_name> <bm:last_document_number>2</bm:last_document_number> <bm:date_added>2016-04-04 12:14:57</bm:date_added> <bm:date_modified>2016-04-06 09:04:18</bm:date_modified> <bm:data_xml> <bm:transaction bm:bs_id=""11111"" bm:buyer_company_name=""test1"" bm:buyer_user_name=""someone"" bm:currency_pref=""GBP"" bm:data_type=""0"" bm:document_name=""Transaction"" bm:document_number=""1"" bm:document_var_name=""transaction"" bm:process_var_name=""XXX_1"" bm:supplier_company_name=""test1""> <bm:_document_number>1</bm:_document_number> <bm:createdBy_t>SomeOne</bm:createdBy_t> <bm:_price_book_var_name>_default</bm:_price_book_var_name> <bm:createdDate_t>2016-04-04 00:00:00</bm:createdDate_t> <bm:currency_t>INR</bm:currency_t> <bm:_customer_t_first_name/> <bm:_customer_t_last_name/> <bm:_customer_t_company_name>Test Account</bm:_customer_t_company_name> </bm:transaction> </bm:data_xml> </bm:transaction> </bm:getTransactionResponse> </soap:Body> </soap:Envelope>"

    xml.SetProperty "SelectionNamespaces", "xmlns:bm=""http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""

    Debug.Print xml.SelectSingleNode("//bm:_customer_t_company_name").Text
End Sub

输出为Test Account。