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 · Accepted Answer · 2017-06-06 11:28:59Z

1

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.

answered Jun 6, 2017 at 11:28

jkpieterse

3,0591 gold badge11 silver badges20 bronze badges

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

5 Comments

sinsedrix Over a year ago

I don't work with SOAP but with new Web Services so I don't know how is my envelope. I'm meant to work with a client class defined in my WSDL.

jkpieterse Over a year ago

In that case I'm unable to help much. But, what triggers a call to the webservice (what do I send to it and what is the URL) and what does the webservice return?

sinsedrix Over a year ago

Finally, I don't want to have dependencies on interfaces so I'll use SOAP enveloppe method.

StingyJack Over a year ago

A list of all the references that are needed would be nice.

jkpieterse Over a year ago

It says: "using the Microsoft XML 3.0 library"

sinsedrix · Accepted Answer · 2017-06-07 07:43:15Z

0

I found a useful post dealing with the subject.

Web Service Software Factory 2010 is not maintained and seems to be obsolete.

edited Jun 7, 2017 at 7:43

answered Jun 6, 2017 at 15:33

sinsedrix

4,8055 gold badges38 silver badges66 bronze badges

Collectives™ on Stack Overflow

Calling WCF service using VBA code in Excel 2010

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related