i'm trying vba work dell soap interface. think i've got 99% of way there request returning data asked cell a5.
i cannot systemmodel node value.
private sub commandbutton1_click() 'set , instantiate our working objects dim surl string dim senv string set xmlhtp = createobject("msxml2.xmlhttp") dim xmldoc new msxml2.domdocument60 surl = "http://xserv.dell.com/services/assetservice.asmx?wsd" ' create our soap envelope submission web service 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 & " <getassetinformation xmlns=""http://support.dell.com/webservices/"">" senv = senv & " <guid>12345678-1234-1234-1234-123456789012</guid>" 'looks sku in active worksheet cell b3 senv = senv & " <applicationname>assetservice</applicationname>" senv = senv & " <servicetags>" & worksheets("sheet1").range("a2").value & "</servicetags>" senv = senv & " </getassetinformation>" senv = senv & " </soap:body>" senv = senv & " </soap:envelope>" ' send soap request xmlhtp .open "post", surl, false .setrequestheader "host", "xserv.dell.com" .setrequestheader "content-type", "text/xml; charset=utf-8" .setrequestheader "soapaction", "http://support.dell.com/webservices/getassetinformation" .setrequestheader "accept-encoding", "zip" .send senv xmldoc.loadxml .responsetext xmldoc.setproperty "selectionnamespaces", "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'" set xmlelement = xmldoc.documentelement worksheets("sheet1").range("a5").value = .responsetext worksheets("sheet1").range("a3").value = .selectsinglenodes("//getassetinformationresult/asset/assetheaderdata/systemmodel").text msgbox xmlelement.selectnodes("body/getassetinformationresult/asset/assetheaderdata/systemmodel").text end dim xmltemplatestring string set xmlroot = xmldoc.documentelement set xmlchildren = xmlroot.childnodes each xmltemplate in xmlchildren xmltemplatestring = xmltemplatestring & xmltemplate.nodename next xmltemplate msgbox xmltemplatestring end sub these 2 lines not work , gets runtime error 438
worksheets("sheet1").range("a3").value = .selectsinglenodes("//getassetinformationresult/asset/assetheaderdata/systemmodel").text msgbox xmlelement.selectnodes("body/getassetinformationresult/asset/assetheaderdata/systemmodel").text
xmltemplatestring returns soap:body
a5 contains following text:
<?xml version="1.0" encoding="utf-8"?><soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:xsd="http://www.w3.org/2001/xmlschema"><soap:body><getassetinformationresponse xmlns="http://support.dell.com/webservices/"><getassetinformationresult><asset><assetheaderdata><servicetag>7y5wr4j</servicetag><systemid>poweredge-r510</systemid><buid>202</buid><region>europe</region><systemtype>poweredge</systemtype><systemmodel>poweredge r510</systemmodel><systemshipdate>2010-05-30t19:00:00-05:00</systemshipdate></assetheaderdata><entitlements><entitlementdata><servicelevelcode>nu</servicelevelcode><serviceleveldescription>nd prosupport enduser on-site extended dates</serviceleveldescription><provider /><startdate>2015-05-30t00:00:00</startdate><enddate>2016-05-31t00:00:00</enddate><daysleft>316</daysleft><entitlementtype>active</entitlementtype></entitlementdata><entitlementdata><servicelevelcode>nu</servicelevelcode><serviceleveldescription>nd prosupport enduser on-site extended dates</serviceleveldescription><provider /><startdate>2013-05-30t00:00:00</startdate><enddate>2015-05-31t00:00:00</enddate><daysleft>0</daysleft><entitlementtype>expired</entitlementtype></entitlementdata><entitlementdata><servicelevelcode>nd</servicelevelcode><serviceleveldescription>next business day onsite</serviceleveldescription><provider /><startdate>2010-05-30t00:00:00</startdate><enddate>2013-05-31t00:00:00</enddate><daysleft>0</daysleft><entitlementtype>expired</entitlementtype></entitlementdata><entitlementdata><servicelevelcode>4i</servicelevelcode><serviceleveldescription>4h prosupport mission extended dates</serviceleveldescription><provider /><startdate>2010-05-30t00:00:00</startdate><enddate>2013-05-31t00:00:00</enddate><daysleft>0</daysleft><entitlementtype>expired</entitlementtype></entitlementdata></entitlements></asset></getassetinformationresult></getassetinformationresponse></soap:body></soap:envelope> any appreciated!
you find recursively through child nodes
eg
set xmlelement = xmldoc.documentelement set systemmodel = findchild(xmlelement, "systemmodel") worksheets("sheet1").range("a3").value = systemmodel.text function findchild(byval rootnode object, byval nodename) object dim node object dim tempfind object each node in rootnode.childnodes if rootnode.nodename = nodename set tempfind = node else if node.nodetypestring = "element" if tempfind nothing set tempfind = findchild(node, nodename) end if end if end if next node set findchild = tempfind end function where findchild(xmlelement, "systemmodel") passes xml node , nodename search
also, there multiple namespaces in xml need include them , ensure prefixed allow correct path defined. should work
private sub commandbutton1_click() dim xmldoc new msxml2.domdocument60 xmldoc.loadxml .responsetext xmldoc.setproperty "selectionnamespaces", _ "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' " & _ "xmlns:xsi='http://www.w3.org/2001/xmlschema-instance' " & _ "xmlns:xsd='http://www.w3.org/2001/xmlschema' " & _ "xmlns:mm='http://support.dell.com/webservices/'" set xmlelement = xmldoc.documentelement set systemmodel = xmlelement.selectsinglenode("//soap:body/mm:getassetinformationresponse/mm:getassetinformationresult/mm:asset/mm:assetheaderdata/mm:systemmodel") worksheets("sheet1").range("a3").value = systemmodel.text msgbox systemmodel.text end sub 
Comments
Post a Comment