Web Services

Consuming Web Services in Excel 2013
In Excel 2013 a new worksheet function was added called WEBSERVICE that allows you to return data from a web service.

Consuming Web Services in Excel 2010 and 2007
In Office 2003 you could access web services using the [[Web Services ToolKit]]
In Office 2007 this toolkit was removed and therefore using just VBA to access web services is no longer possible.
The alternative is to create a Visual Studio solution using a .NET programming language.

Removed in 2007
This library was removed and is no longer available or supported.
This library is not available for Windows 7 or higher.
If you have any VBA code that uses the web services toolkit then you should migrate this code to a Visual Studio (.NET) solution.
https://msdn.microsoft.com/en-us/library/dd819156(v=office.12).aspx

Web Services Toolkit

In Office 2002 a toolkit was available that gave you quick access to Web Services from VBA.
In Office 2003 this toolkit was upgraded to the Office 2003 Web Services Toolkit 2.01.
This toolkit was also known as the Web Services Reference Tool.
This allowed developers to use the UDDI Business Registry or the URL to a WSDL file to reference XML Web services.
This toolkit installed a number of additional DLLs two of which were the Soap Type Library and the [[Microsoft XML Library]].
This toolkit supported Soap 3.0

What is the Soap Type Library ?
Soap is an abbreviation for Simple Object Access Protocol and is an XML messaging framework.
It uses XML technologies and can be used to exchange structured information and has been designed to be programming model independent.
It can be used over all the following protocols: TCP, HTTP, SMTP and MSMQ This library works in conjunction with the Microsoft XML Library and is SOAP can be used to provide a basic messaging framework for web services.
The XML based protocol consists of 3 parts:
Envelope - this defines the message structure and how to process it
Rules - these are encoding rules for expressing the data types
Convention - how to represent the calls and responses

MSDN - Aa140268

Alternatively you can use the XML library

Set oXML = CreateObject("MSXML2.XMLHTTP") 
oXML.Open 
oXML.Send 

Dim myservice As new clsws_Name 

Get Data from a Web Service

Tools > References - Microsoft Soap Type Library
C:\Program Files\Common Files\Microsoft Shared\Office12\MSSOAP30.dll
You can use the web services toolkit or you can just reference the type library
KB - 304265

Private Const c_WSDL_URL As String = "http:///server/services.asmx?wsdl" 
Private sc_Service1 As MSSOAPLib30.SoapClient 

Private Sub CommandButton_Click() 
Dim oDOMNodeList As MSXML2.DOMNodeList 
Dim oXmlDoc As New MSXML2.DOMDocument 
Dim report As MSXML2.IXMLDOMNode 
Dim paramfund As String 
Dim paramdate As Date 
Dim paramreportname As String 
Dim i As Integer 
Dim str_WSML As String 

   On Error GoTo ErrorHandler 
   str_WSML = "" 
   Set sc_Service1 = New MSSOAPLib.SoapClient 
   sc_Service1.MSSoapInit c_WSDL_URL 

   paramfund = "FD1" 
   paramdate = "01/07/2015 01:00:00" 
   paramreportname = "RegionalBreakdown" 
   Set oDOMNodeList = sc_Service1.RunQuery(paramfund, paramdate, paramreportname) 
   If Not oDOMNodeList Is Nothing Then 

    oXmlDoc.LoadXml oDOMNodeList(1).XML 
   Set oDOMNodeList = oXmlDoc.SelectNodes("//element:tag/element") 

   For I = 0 To (oDomNodeList.Length - 1) 

       Debug.Print oDomNodeList(i).selectSingleNode("Region").Text 

   Next i 

   End If 
End Sub