{"id":9921,"date":"2023-03-16T16:49:05","date_gmt":"2023-03-16T21:49:05","guid":{"rendered":"https:\/\/itsoftware.com.co\/content\/?p=9921"},"modified":"2023-03-16T16:49:52","modified_gmt":"2023-03-16T21:49:52","slug":"consuming-webservices-microsoft-excel","status":"publish","type":"post","link":"https:\/\/itsoftware.com.co\/content\/consuming-webservices-microsoft-excel\/","title":{"rendered":"Consuming webservices from Microsoft Excel – GET and POST"},"content":{"rendered":"\n

In this article I will discuss how to consume WebServices from Microsoft excel, using this program as a simple client for WebServices without authentication.<\/p>\n\n\n

\n
\"Consuming
Consuming WebServices from Excel<\/figcaption><\/figure>\n<\/div>\n\n\n

It is worth remembering that a WebService is a means of enabling data transactions between heterogeneous applications.<\/p>\n\n\n\n

A WebService<\/strong>, 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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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:<\/p>\n\n\n\n

1. Consuming WebServices with GET methods<\/strong><\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

Example:<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

For that we will use the following WebService:<\/p>\n\n\n\n

https:\/\/free.currencyconverterapi.com\/api\/v6\/convert?q=USD_COP&compact=ultra<\/pre>\n\n\n\n
where we can see the GET parameters in q= USD_COP<\/strong><\/em> y compact=ultra<\/em><\/strong><\/p>\n\n\n\n
To consume the WebService, enter the url inside the formula WEBSERVICE, as follows:<\/p>\n\n\n\n
=WEBSERVICE(\"https:\/\/free.currencyconverterapi.com\/api\/v6\/convert?q=USD_COP&compact=ultra\")<\/pre>\n\n\n\n
When we finish entering it, it returns the rate of change in a compact JSON format like this:<\/p>\n\n\n\n
{\"USD_COP\":3078.25}<\/pre>\n\n\n\n
To extract the value, and change the period (.) for a comma (,), which is the decimal format used in Colombia, we use the following formula:<\/p>\n\n\n\n
=REPLACE(EXTRACT(F1;FIND(\":\";F1)+1;LEN(F1)-FIND(\":\";F1)-1);\".\";\",\")<\/pre>\n\n\n\n
Thus our example in Excel would look like this:<\/p>\n\n\n
\n
\"Consuming
Consuming webservices in Excel GET method<\/figcaption><\/figure>\n<\/div>\n\n\n
Each time this page is accessed, the value in the exchange rate cell will be recalculated with the value returned by the WebService.<\/p>\n\n\n\n
2. Consuming Webservices with POST method<\/strong><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
So for this type of WebServices it is required to create a macro for sending the POST parameters.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
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:<\/p>\n\n\n
\n
\"Consuming
Consuming webservices in Excel POST method<\/figcaption><\/figure>\n<\/div>\n\n\n
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:<\/p>\n\n\n
\n
\"Consume
Consume webservices in Excel POST Consume<\/figcaption><\/figure>\n<\/div>\n\n\n
Now we include the following code for webservice consumption:<\/p>\n\n\n\n
Sub Webservice()\n\n' <\/strong><\/span>The data headers are entered on the sheet.<\/strong>\nActiveWorkbook.Sheets(\"Hoja1\").Range(\"A:F\").Clear\nActiveWorkbook.Sheets(\"Hoja1\").Range(\"A\" & 1).Value = \"Fecha\"\nActiveWorkbook.Sheets(\"Hoja1\").Range(\"B\" & 1).Value = \"Ubicaci\u00f3n\"\nActiveWorkbook.Sheets(\"Hoja1\").Range(\"C\" & 1).Value = \"CC\"\nActiveWorkbook.Sheets(\"Hoja1\").Range(\"D\" & 1).Value = \"Lista\"\nActiveWorkbook.Sheets(\"Hoja1\").Range(\"E\" & 1).Value = \"Hora Ingreso\"\nActiveWorkbook.Sheets(\"Hoja1\").Range(\"F\" & 1).Value = \"Hora Salida\"\n\n' <\/span>The WebService input parameters are stored, located in cells <\/strong>\nfi = ActiveWorkbook.Sheets(\"Hoja1\").Cells(2, 8).Value\nff = ActiveWorkbook.Sheets(\"Hoja1\").Cells(2, 9).Value\nUbicacion = ActiveWorkbook.Sheets(\"Hoja1\").Cells(2, 10).Value\n\n' <\/span>The object that will consume the webservice is created\n<\/strong>Set objHTTP = CreateObject(\"MSXML2.ServerXMLHTTP\")\n\n' <\/span>The object that will store the result of the webservice in XML format is created.\n<\/strong>Set xmlDoc = CreateObject(\"MSXML2.DOMDocument\")\n\nURL = \"http:\/\/localhost:57371\/WebService.asmx\/GetData\"\n\n' <\/span>The webservice is managed with its URL and other parameters<\/strong>.\nobjHTTP.Open \"POST\", URL, False\nobjHTTP.setRequestHeader \"Content-Type\", \"application\/x-www-form-urlencoded\"\nPayload = \"fi=\" + fi + \"&ff=\" + ff + \"&ubicacion=\" + Ubicacion\nobjHTTP.send (Payload)\n\n' <\/span>The response XML is saved<\/strong>\nxmlDoc.LoadXML objHTTP.responseText\n\n' <\/span>XML nodes are read\n<\/strong>Set FechaNodes = xmlDoc.SelectNodes(\"\/ArrayOfResultado\/Resultado\/fecha\/text()\")\nSet CCNodes = xmlDoc.SelectNodes(\"\/ArrayOfResultado\/Resultado\/cc\/text()\")\nSet HoraiNodes = xmlDoc.SelectNodes(\"\/ArrayOfResultado\/Resultado\/horai\/text()\")\nSet HorafNodes = xmlDoc.SelectNodes(\"\/ArrayOfResultado\/Resultado\/horaf\/text()\")\nSet UbicacionNodes = xmlDoc.SelectNodes(\"\/ArrayOfResultado\/Resultado\/ubicacion\/text()\")\nSet ListaNodes = xmlDoc.SelectNodes(\"\/ArrayOfResultado\/Resultado\/lista\/text()\")\n\n' <\/span>The XML data is written in the rows of the Excel sheet.<\/strong>\nFor i = 0 To (FechaNodes.Length - 1)\n  Fecha = FechaNodes(i).NodeValue\n  CC = CCNodes(i).NodeValue\n  Horai = HoraiNodes(i).NodeValue\n  Ubicacion = UbicacionNodes(i).NodeValue\n  Lista = ListaNodes(i).NodeValue\n  Horaf = HorafNodes(i).NodeValue\n\n  ActiveWorkbook.Sheets(\"Hoja1\").Range(\"A\" & i + 2).Value = Fecha\n  ActiveWorkbook.Sheets(\"Hoja1\").Range(\"B\" & i + 2).Value = Ubicacion\n  ActiveWorkbook.Sheets(\"Hoja1\").Range(\"C\" & i + 2).Value = CC\n  ActiveWorkbook.Sheets(\"Hoja1\").Range(\"D\" & i + 2).Value = Lista\n  ActiveWorkbook.Sheets(\"Hoja1\").Range(\"E\" & i + 2).Value = Horai\n  ActiveWorkbook.Sheets(\"Hoja1\").Range(\"F\" & i + 2).Value = Horaf\nNext\n\nEnd Sub<\/pre>\n\n\n\n
Finally, when we press the Update Data button, we will have the data consumed from the WebService, organized in the Excel sheet:<\/p>\n\n\n
\n
\"Consume
Consume webservices in Excel POST Final Consumption<\/figcaption><\/figure>\n<\/div>\n\n\n
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.<\/p>\n\n\n\n
This article is part of the ITSoftware SAS<\/a><\/strong> knowledge difussion system.<\/p>\n\n\n\n
If you liked it, please don’t forget to share it on social networks. \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"
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[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[305,1498,229],"tags":[1522,1521],"class_list":["post-9921","post","type-post","status-publish","format-standard","hentry","category-automatizacion","category-excel","category-ms-office","tag-servicioweb","tag-webservice"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/posts\/9921","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/comments?post=9921"}],"version-history":[{"count":0,"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/posts\/9921\/revisions"}],"wp:attachment":[{"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/media?parent=9921"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/categories?post=9921"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itsoftware.com.co\/content\/wp-json\/wp\/v2\/tags?post=9921"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}