Consuming webservices from Microsoft Excel – GET and POST

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.

Example:

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:

https://free.currencyconverterapi.com/api/v6/convert?q=USD_COP&compact=ultra

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:

=WEBSERVICE("https://free.currencyconverterapi.com/api/v6/convert?q=USD_COP&compact=ultra")

When we finish entering it, it returns the rate of change in a compact JSON format like this:

{"USD_COP":3078.25}

To extract the value, and change the period (.) for a comma (,), which is the decimal format used in Colombia, we use the following formula:

=REPLACE(EXTRACT(F1;FIND(":";F1)+1;LEN(F1)-FIND(":";F1)-1);".";",")

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.

Example:

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. 😉