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...



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/WSDeta...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