Submitting a SOAP Request via Excel / VB

[-Wayner-]

Hello,

I've given this a good search before posting, but a lot of the answers are really unclear, often referring to the need for add-ins (Microsoft Web Services Toolkit for example - but this only appears to be relevant for Excel 2003 and before). Therefore looking for a quick steer please...

I'm looking to run a really simple soap request via VB. I have a request in a worksheet (which I can copy in to a module if needed) and a WSDL address in the format 'http://addressort/location/location1'.

I just want to fire the request from excel to this address and pass the response back into a worksheet for analysis.

Can this be done in excel 2010 off the shelf without the introduction of add-ins, or if I do need additional installations, what is the minimum required? If it can be done can someone provide a brief example of the code required to wrap around my request to trigger this?

I don't need to do anything specifically fancy with building or analysing the request - just be able to submit it to the SOAP service via the provided endpoint. I'd really appreciate if someone can provide an overview / steps of what's needed to do this?

Advice is very much appreciated.

Thanks,
Wayne

 

[Kyle123]

You don't need addins, you just need to build the xml, have a read here: Accessing Web Service From Excel 2013 [SOLVED]

 

[-Wayner-]

Hi Kyle,

Thanks for the pointer - that look exactly like what i'm trying to do, but i'd love some additional input if possible please as i'm struggling to translate the original discussion to my requirement!

I am trying to take the below

Sub test()
    Dim doc     As DOMDocument60
    Dim req     As XMLHTTP
    Dim node    As IXMLDOMElement
    Dim root    As Object
    
    Dim response As DOMDocument
    
    Set doc = New DOMDocument60
    
    Set root = doc.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
    doc.appendChild root
    
    Set node = doc.appendChild(doc.createElement("soap:Envelope"))
    With node
        .setAttribute "xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance"
        .setAttribute "xmlns:xsd", "http://www.w3.org/2001/XMLSchema"
        .setAttribute "xmlns:soap", "http://schemas.xmlsoap.org/soap/envelope/"
        With .appendChild(doc.createElement("soap:Body"))
            Set node = .appendChild(doc.createElement("GetQuote"))
            node.setAttribute "xmlns", "http://www.webserviceX.NET/"
            node.appendChild(doc.createNode(NODE_ELEMENT, "symbol", "http://www.webserviceX.NET/")).Text = "GOOG"
        End With
    End With
    
    Set req = New XMLHTTP
    req.Open "POST", "http://www.webservicex.net/stockquote.asmx", False
    req.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    req.setRequestHeader "SOAPAction", "http://www.webserviceX.NET/GetQuote"
    req.setRequestHeader "Content-Length", Len(doc.XML)
    req.send doc.XML
    
    Set response = New DOMDocument
    response.LoadXML (req.responseText)
    
    response.LoadXML response.SelectSingleNode("//GetQuoteResult").Text
    
    Debug.Print response.SelectSingleNode("//Name").Text, response.SelectSingleNode("//Date").Text, response.SelectSingleNode("//High").Text

    
End Sub

As this seems to be the SOAP based query. What i'm struggling with is what are the variables. I.e. where can I specify my endpoint to adapt the above to work for my request. Currently my request is embeded within a worksheet, but I was assuming I could reference the target range and submit that via the request in VBA.

Any advice on how I can modify the above would be very much appreciated!

Thanks,
Wayne

 

[-Wayner-]

Note, if I try to run the above as is I get a 'user defined type not defined' error on the first line.

This is clearly stretching the limits of my basic VBA skills!

 

[-Wayner-]

Same if I try to just use the post example (this looked simpler so thought i'd try this instead:

Sub POSTExample()

    Dim request     As XMLHTTP
    Dim myValue     As String
    Dim doc         As DOMDocument60
    
    myValue = Range("Request")
    
    Set request = New XMLHTTP
    Set doc = New DOMDocument60
    
    With request
        .Open "POST", "<MY ENDPOINT ADDRESS HERE>", False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send myValue
        doc.LoadXML (.responseText)
    End With

    Debug.Print doc.Text
    
End Sub

I've updated the endpoint value and I've updated the range which hold my request.

But again, it's not even getting to the post action, it's failing on the definitions at the top saying they aren't defined. Is there something I need to do to make 'XMLHTTP' & 'DOMDocument60' intelligible to my request?

Thanks again.

Wayne

 

[Kyle123]

You need to add the relevant references, off the top of my head Microsoft XML vxx, but it's probably in that thread - that will resolve the undefined types

 

[-Wayner-]

Really sorry, I can't see any reference to the above or any definitions in the original thread.

The POST request looks fairly simple to update and to work (assuming it's a standalone piece of functionality), but not sure how to set the definitions. Would really appreciate a pointer if you have a moment.

Thanks again for your help.

 

[Kyle123]

Go to Tools > References > Tick "Microsoft XML, vXX", the highest one you've got

Not all SOAP services support POST and GET, the service description should tell you

 

[-Wayner-]

Got it - thanks. References added (although only works on v4.0 rather than v6.0) and i can now successfully hit the services and get a response. I can do that if the request is embedded within the code.

However, the final missing piece is to use data in a worksheet to populate the request.

i tried to set:

myValue = Range("Request")

but this just gives me a type mismatch.

Any idea how i can use a range in my work book to populate the request?