Accessing Web Service From Excel 2013 with Input Parameters

[DeepDave]

Hi,

I have some Web Services that I need to access from Excel VBA.

The WebServices have certain functions that I need to call..

I will pass in the required parameters from Excel Cells.

The problem is, MSOSOAPLib30.SoapClient30 isn't accessible from Excel 2013, hence I am trying it with MSXML2

For trial, I'v got a Web-service made thats asks me a name, and when I invoke it, it returns a String, for e.g. Good Morning John

Can anyone help me with the same?

Thank You,
Deep Dave

[Kenneth Hobs]

Welcome to the forum!

After reviewing these threads, post back here if you have questions.

http://www.vbaexpress.com/forum/showthread.php?p=252560
http://www.vbaexpress.com/forum/showthread.php?t=34354

[SamT]

Dave,

Welcome to VBA Express, IMO, the bet help site for all things Microsoft Office.

I am sorry that you haven't received a response from our web experts yet. Perhaps if you post the code you already have, (click on the # icon on the editor, then past each piece of code between the '['CODE']' Tags,) and post the url for us, it will help.Note that you can't actually post a url until you have 5 posts. you can make 4 posts in the "Testing Area" folder or insert breaks in the "http_//_www" part.

[DeepDave]

Hi,

Thank you both for the reply.. The threads shared by you are closed to what I am looking for, but I am unable to get them working..

I'v made a sample WebService for the ease of VBA Coding.. We input a Name from Range("A2"), and in return it returns a string that says

Good Morning John (Or whatever name is entered in Range A2)

I am not sure which of these lines work, and which are incorrect.. Was just trying out different things... I am unable to add the code as it gives me an error that says, usage of forbidden words, hence added an image... :think:



Cheers!

Deep :)

[DeepDave]

Hi,

Please ignore the previous post..

Thank you both for the reply.. The threads shared by you are close to what I am looking for, but I am unable to get them working..

For ease of understanding, I have this WebService (Unable to post link yet) which requires only 1 Input; the Stock Name. (For Eg. AAPL, GOOG etc)

I will give my Input parameter from cell A2 (For Eg. AAPL, GOOG etc) and the XML Tag Values I want to return are <Open> <High> <PercentageChange> in Cell D1, D2, D3

If I get this, I can work out the code for my actual web service easily..

Cheers!

Deep :)

[DeepDave]

Pasting the link in the below

h t t p : / / w w w . w e b s e r v i c e x . n e t / W S / W S D e t a i l s . a s p x ? W S I D = 9 & C A T I D = 2

Please find and replace space from the link..

[SamT]

Corrected Link:
http://www.webservicex.net/WS/WSDetails.aspx?WSID=9&CATID=2

[Kenneth Hobs]

Before I work on this, if you want to try yourself, this thread might be of more use. http://www.vbaexpress.com/forum/showthread.php?t=42251

The screen print is not clear for me. If you are having a problem, you could attach the file. Click the Go Advanced button on lower right of reply and then the paperclip icon and browse to your file.

[Kenneth Hobs]

I don't have time to finish this but it should get you close. It is returning the html values for < and > which will require more work. It is just a matter of parsing what you need via MSXML or standard VBA methods now.


Sub Test_GetQuote() Dim s As String
s = GetQuote("Goog")
MsgBox s
End Sub


Function GetQuote(sStock As String) As String
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP
Dim xmlDoc As New DOMDocument
sURL = "http://www.webservicex.net/stockquote.asmx?WSDL"

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 & " <GetQuote xmlns=""http://www.webserviceX.NET/"">"
sEnv = sEnv & " <symbol>" & sStock & "</symbol>"
sEnv = sEnv & " </GetQuote>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"


With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Host", "www.webservicex.net"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction", "http://www.webserviceX.NET/GetQuote"
.send sEnv
'xmlDoc.LoadXML .responseText
'xmlDoc.Save ThisWorkbook.Path & "\WebQueryResult.xml"
GetQuote = .responseText
End With
End Function

[Kenneth Hobs]

Here is the code that DeepDave used.


Sub POSTExample() Dim request As XMLHTTP
Dim myValue As String
Dim doc As DOMDocument60

myValue = "GOOG"

Set request = New XMLHTTP
Set doc = New DOMDocument60

With request
.Open "POST", "http://www.webservicex.net/stockquote.asmx/GetQuote", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.send "symbol=" & myValue
doc.LoadXML .responseText
End With

doc.LoadXML doc.Text

Debug.Print doc.SelectSingleNode("//Name").Text, doc.SelectSingleNode("//Date").Text, doc.SelectSingleNode("//High").Text
End Sub


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