VBA Excel宏SelectSingleNode不返回任何内容 [英] VBA Excel Macro SelectSingleNode returns nothing

问题描述

我第一次使用excel宏，我编写了代码来解析网络服务响应，我想更新excel中的单个单元格.

My very first venture with excel macro, I have written code to parse a web service response and I would like to update tag values in individual cells in excel.

以下是我的XML的摘录(巨大的Web服务响应)

Following is the excerpt of my XML (a huge web service response)

<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>

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

Below is the code, I've been using.

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")始终返回Nothing.

.SelectSingleNode("//soap:Body") works fine. the .SelectSingleNode("//bm:_customer_t_company_name") always returns Nothing when I test my code. So is the

.SelectSingleNode("//bm:getTransactionResponse")，什么也不返回.

.SelectSingleNode("//bm:getTransactionResponse"), returns nothing.

能不能让我知道我做错了什么?

Can you please let me know what I am doing wrong ?

这是完整XML结构的屏幕截图在此处输入图像描述

Here is a screenshot of full XML structureenter image description here

推荐答案

您提供的代码没有问题.您应该检查Web服务实际返回的XML，以确保它具有您要查找的标签.

Nothing's wrong with the code as you presented it. You should examine the XML that the web service is actually returning to make sure that it has the tag that you're looking for.

我将您的示例简化为以下VBA Sub，该VBA Sub运行无误.

I reduced your example to the following VBA Sub, which runs without error.

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

输出为测试帐户.

这篇关于VBA Excel宏SelectSingleNode不返回任何内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！