Calling WCF service using VBA code in Excel 2010

Question

I'm trying to call an ASP.Net WCF service in XML from VBA in Excel 2010.

I read this question: calling web service using VBA code in excel 2010 but I can't use a third party library, XML or JSON.

Is there a way to do it natively with VBA working with web services?

EDIT I've installed SOAP Toolkit 3.0 and SOAP Toolkit 3.0 Software Update.

I've added these libraries to my workbook:

Microsoft XML, v3.0
Microsoft Soap Type Library v3.0
WinHttp Soap Connector Type Library
WinInet Soap Connector Type Library
Microsoft Soap WSDL File Generator v3.0

I think I have to add a web reference to my service into my workbook by "Tools->Add a web reference" menu, but this option doesn't appear. What should I do to see it ?

jkpieterse jkpieterse · Accepted Answer · 2017-06-06T11:28:59

This is an example routine that fetches data from a webservice using the Microsoft XML 3.0 library:

Sub DoIt()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlHtp As New MSXML2.XMLHTTP
    Dim xmlDoc As New DOMDocument
    Dim oValueNodes As MSXML2.IXMLDOMNodeList
    sURL = "http://webservices.gama-system.com/exchangerates.asmx?op=CurrentConvertToEUR"

    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 & "    <CurrentConvertToEUR xmlns=""http://www.gama-system.com/webservices"">"
    sEnv = sEnv & "      <dcmValue>100</dcmValue>"
    sEnv = sEnv & "      <strBank>BS</strBank>"
    sEnv = sEnv & "      <strCurrency>USD</strCurrency>"
    sEnv = sEnv & "      <intRank>1</intRank>"
    sEnv = sEnv & "    </CurrentConvertToEUR>"
    sEnv = sEnv & "  </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"

    With xmlHtp
        .Open "post", sURL, False
        .setRequestHeader "Host", "webservices.gama-system.com"
        .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        .setRequestHeader "soapAction", "http://www.gama-system.com/webservices/CurrentConvertToEUR"
        .send sEnv
        xmlDoc.loadXML .responseText
        Set oValueNodes = xmlDoc.getElementsByTagName("CurrentConvertToEURResponse")
        MsgBox oValueNodes.Item(0).nodeTypedValue
    End With
     'xmlDoc.Save ThisWorkbook.Path & "\WebQueryResult.xml"
End Sub

Key thing you need is the exact structure of the SOAP envelope the webservice is expecting.

Calling WCF service using VBA code in Excel 2010

2 Answers