VBA_WebService_IXMLDOMNodeList ??

In a nutshell, I have to write an Excel / Word macro what will consume a Web Service that will access an Oracle database to extract some information back to an Excel or Word template.  I'm new to all of these technologies: VBA, Web Service and XML.  After doing some reading online, I’ve figured out how to write some VBA code to call a web service, which involves simple input/output parameters such as a string or an integer.  But, I don’t know how to call a web service method, where the input parameter is defined as “IXMLDOMNodeList”.   To be more specific, the method I’m trying to call is:

 

 

Public Function wsm_AccountQueryPage(ByVal any_AccountWS_AccountQueryPage_Input As MSXML2.IXMLDOMNodeList) As MSXML2.IXMLDOMNodeList

 

I don’t have any more information than this.  I don’t know how to call this method. How do I create an object of MSXML2.IXMLDOMNodeList type?  Is there anymore information I should get before trying to tackle this? 

 

Any help will be appreciated!

Thanks in advance!

 

IXMLDOMNodeList is a collection of XML nodes that if I am not wrong theoretically need not really belong to the same XML document.

You would probably want to create a complete DOMDocument that serves as the XML content of the web service's parameters, then obtaining its child nodes, which is represented as IXMLDOMNodeList

http://www.topxml.com/xml/articles/dom_xml_prog/default-05.asp

Thanks for the reply.

I understand that I have to create an object of IXMLDOMNodeList and pass it to the function to call it.  All I have is the WSDL file of the web service... from the wsdl file, I was able to extract the following information w.r.t the method I'm trying to call:

- <operation name="AccountQueryPage">
  <input message="tns:AccountWS_AccountQueryPage_Input" />
  <output message="tns:AccountWS_AccountQueryPage_Output" />
  </operation>

- <message name="AccountWS_AccountQueryPage_Input">
  <part name="AccountWS_AccountQueryPage_Input" element="tns:AccountWS_AccountQueryPage_Input" />
  </message>

- <message name="AccountWS_AccountQueryPage_Output">
  <part name="AccountWS_AccountQueryPage_Output" element="tns:AccountWS_AccountQueryPage_Output" />
  </message>

- <xsd:element name="AccountWS_AccountQueryPage_Input">
- <xsd:complexType>
- <xsd:sequence>
  <xsd:element name="UseChildAnd" type="xsd:string" />
  <xsd:element name="PageSize" type="xsd:string" />
  <xsd:element ref="xsdLocal1:ListOfAccount" />
  <xsd:element name="StartRowNum" type="xsd:string" />
  </xsd:sequence>
  </xsd:complexType>
  </xsd:element>

- <xsd:element name="AccountWS_AccountQueryPage_Output">
- <xsd:complexType>
  - <xsd:sequence>
  <xsd:element name="LastPage" type="xsd:string" />
  <xsd:element ref="xsdLocal1:ListOfAccount" />
  </xsd:sequence>
  </xsd:complexType>
  </xsd:element>

 

And the method's input/output parameters are defined in blue.. but I'm not sure how to create the IXMLDOMNodeList from the information given above. 

You can probably use a 3rd party XML services program that can consume WSDLs and generate the necessary SOAP requests

http://www.oxygenxml.com/wsdl_editor.html

http://www.altova.com/features_soap.html

Once you know how the SOAP content is like, you can recreate it in VBA code.

Hi Sweetpea,

   I am trying to invoke webservice from excel and I have the same issue with input and output as MSXML2.IXMLDOMNodeList,

 

 I  downloaded oxygen and I could get the SOAP message and response from WSDL analyzer but I am not able to understand how I have to send the same message in Excel. Could you pl lemme know if you have figured out?

Thanks,

Prasad

Try generating a DOMDocument containing a <AccountWS_AccountQueryPage_Input> element with the correct internal element structure as defined in the WSDL document. Together with the values you want to use as input, of course. Then extract it out of the DOMDocument as an IXMLDOMNodeList collection.