In this article I will discuss how to consume WebServices from Microsoft excel, using this program as a simple client for WebServices without authentication.
It is worth remembering that a WebService is a means of enabling data transactions between heterogeneous applications.
A WebService, installed on a machine, exposes a series of methods that allow well-defined transactions with its database, such as: consulting certain information, adding, editing or deleting records.
Thus, a WebService defines restricted mechanisms with which certain transactions can be carried out on the data of a server. To make them more secure, authenticated and SSL-encrypted connections are generally used.
In Microsoft Excel it is possible to consume WebServices without authentication to integrate information from other sources, without having to import or copy them from their origins. In Excel it is possible to consume WebServices that use GET and POST methods to pass parameters to the server. In the following we will discuss in more detail the use of each of them:
1. Consuming WebServices with GET methods
To consume WebServices with sending parameters by the GET method, a formula called WEBSERVICE(url) is used, which receives as parameter the url of the webservice and its variables.
In this example we want to use a WebService to bring us the current exchange rate from COP$ to USD$, in order to update the prices in Colombian pesos of some products with prices in dollars.
For that we will use the following WebService:
where we can see the GET parameters in q= USD_COP y compact=ultra
To consume the WebService, enter the url inside the formula WEBSERVICE, as follows:
When we finish entering it, it returns the rate of change in a compact JSON format like this:
To extract the value, and change the period (.) for a comma (,), which is the decimal format used in Colombia, we use the following formula:
Thus our example in Excel would look like this:
Each time this page is accessed, the value in the exchange rate cell will be recalculated with the value returned by the WebService.
2. Consuming Webservices with POST method
When we have a WebService to which the parameters are sent by the POST method, the WEBSERVICE formula cannot be used. This happens because when using the POST method the parameters are not sent in the url.
So for this type of WebServices it is required to create a macro for sending the POST parameters.
This time we are going to work with a WebService that delivers input and output information of a location, from a start date to an end date:
Then our goal is to consume it and organize it in an Excel sheet. In the Excel sheet we reserve some cells to include the webservice input parameters (Start Date, End Date and Location), and a button called Update Data to consume the WebService with the parameters indicated on the left:
Now we include the following code for webservice consumption:
Sub Webservice() ' The data headers are entered on the sheet. ActiveWorkbook.Sheets("Hoja1").Range("A:F").Clear ActiveWorkbook.Sheets("Hoja1").Range("A" & 1).Value = "Fecha" ActiveWorkbook.Sheets("Hoja1").Range("B" & 1).Value = "Ubicación" ActiveWorkbook.Sheets("Hoja1").Range("C" & 1).Value = "CC" ActiveWorkbook.Sheets("Hoja1").Range("D" & 1).Value = "Lista" ActiveWorkbook.Sheets("Hoja1").Range("E" & 1).Value = "Hora Ingreso" ActiveWorkbook.Sheets("Hoja1").Range("F" & 1).Value = "Hora Salida" ' The WebService input parameters are stored, located in cells fi = ActiveWorkbook.Sheets("Hoja1").Cells(2, 8).Value ff = ActiveWorkbook.Sheets("Hoja1").Cells(2, 9).Value Ubicacion = ActiveWorkbook.Sheets("Hoja1").Cells(2, 10).Value ' The object that will consume the webservice is created Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") ' The object that will store the result of the webservice in XML format is created. Set xmlDoc = CreateObject("MSXML2.DOMDocument") URL = "http://localhost:57371/WebService.asmx/GetData" ' The webservice is managed with its URL and other parameters. objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" Payload = "fi=" + fi + "&ff=" + ff + "&ubicacion=" + Ubicacion objHTTP.send (Payload) ' The response XML is saved xmlDoc.LoadXML objHTTP.responseText ' XML nodes are read Set FechaNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/fecha/text()") Set CCNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/cc/text()") Set HoraiNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/horai/text()") Set HorafNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/horaf/text()") Set UbicacionNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/ubicacion/text()") Set ListaNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/lista/text()") ' The XML data is written in the rows of the Excel sheet. For i = 0 To (FechaNodes.Length - 1) Fecha = FechaNodes(i).NodeValue CC = CCNodes(i).NodeValue Horai = HoraiNodes(i).NodeValue Ubicacion = UbicacionNodes(i).NodeValue Lista = ListaNodes(i).NodeValue Horaf = HorafNodes(i).NodeValue ActiveWorkbook.Sheets("Hoja1").Range("A" & i + 2).Value = Fecha ActiveWorkbook.Sheets("Hoja1").Range("B" & i + 2).Value = Ubicacion ActiveWorkbook.Sheets("Hoja1").Range("C" & i + 2).Value = CC ActiveWorkbook.Sheets("Hoja1").Range("D" & i + 2).Value = Lista ActiveWorkbook.Sheets("Hoja1").Range("E" & i + 2).Value = Horai ActiveWorkbook.Sheets("Hoja1").Range("F" & i + 2).Value = Horaf Next End Sub
Finally, when we press the Update Data button, we will have the data consumed from the WebService, organized in the Excel sheet:
As we can see, the WebServices consumption functions allow transactions from Excel to other applications, no matter in which architecture, database or programming languages they were built, only the methods and parameters needed to exchange information between applications are required.
This article is part of the ITSoftware SAS knowledge difussion system.
If you liked it, please don’t forget to share it on social networks. 😉