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.
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 datatypes
Convention - how to represent the calls and responses
MSDN - Aa140268
Alternatively you can use the XML library
Set oXML = CreateObject("MSXML2.XMLHTTP")
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
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
Set oDOMNodeList = oXmlDoc.SelectNodes("//element:tag/element")
For I = 0 To (oDomNodeList.Length - 1)