Submitting Soap XML using VBA

Question

When using SoapUI 5.3, I can submit a request successfully using the request editor (to a web service). I am attempting to submit the file using Excel VBA but am not able to determine if this completed properly or not.

I am using the VBA code below, but have only 1 days experience with XML. I think the problem may be in the prolog or header, but I am not sure what this should be as there is no header on the request in SoapUI. I have the reference to Microsoft XML 6.0 set.

Sub SendXMLtoSAA()
    Dim myHTTP As New MSXML2.XMLHTTP60
    Dim myDom As New MSXML2.DOMDocument60
    Dim myXML As String

    ''myDom.LoadXML (myXML)
    myDom.Load "C:\Users\me\Desktop\test.xml"
    myHTTP.Open "post", myServer, False

    myHTTP.send (myDom.XML)
    MsgBox myHTTP.responseText    
End Sub

I added <?xml version='1.0' encoding='UTF-8'?> to the start of the XML code but no matter what values are in the fields, I get the same responsetext and do not know if it has been 'Posted' properly and if the values are acceptable. Should I be looking at some other value?

EDIT: It appears that the myHTTP.responseText is showing the original webservice XML.

RE-Edit: This is because I had not removed wsdl from the server link.

The other code I had posted below was incomplete and turns out to be unnecessary, so I have removed it so that others do not get confused by it.

Thanks to all who had a look at this for me.

I suggest that you install a sniffer of some kind (Wireshark or something else) and look at what you are sending from SoapUI vs from Excel. — Sam
– Sam, Commented Jul 18, 2017 at 12:46

Glib · Accepted Answer · 2017-07-20 19:48:20Z

2

Well, I said I had no experience with XML and what was wrong just goes to prove it.

For the server, I had it ending in ...ls.php?wsdl&test but I should have removed the wsdl from that and had just ...ls.php?test

The other sticking point was the variable type for myHTTP. I had this as MSXML2.XMLHTTP60 but I believe this blocks the response as a security measure for cookies. After amending to WinHttp.WinHttpRequest, it looks like that does the trick.

answered Jul 20, 2017 at 19:48

Glib

2622 gold badges6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Namphibian Over a year ago

Level one experience gained!

Collectives™ on Stack Overflow

Submitting Soap XML using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related