POST #031
=======================================================================
Hi everyone, welcome back, thanks for listening, if you like this video I'm warleyalex from the beautiful Seven lakes, Brazil. otherwise I don't know anything about it... Today I’m gonna talk web services with Microsoft Excel 2003. If you don’t know, Web Services are reusable applications that expose an interface that can be accessed over the internet. Web services generally use XML to provide a platform and language independent implementation of distributed application services and information.
Web services allow us to utilize the services provided by the other organizations. We can consume the web services not only from applications developed using various programming languages but also from Microsoft office tools like Access, Excel, and Word etc. In this mini-video we will explore how we can access a web service from Microsoft Excel using Delphi mORMot!
I was trying to write some VBA code in Excel 2003 that would consume a web service created with Delphi. So I would like to create in pure VBA (visual basic for application) without installing any plugins, neither opening up Visual Studio, nor other hacks. Some fun features include strong authentication, URI signatures and JSON support (thanks to vba-json).
I’m using a REST server. REST is simple enough in practice that it doesn't really need a toolkit. All it requires is support for HTTP, which VBA offers through the MSXML2.XMLHTTP object. Using this object, you can create HTTP requests and process the responses. There's no need to use the Microsoft Office Web Services Toolkit for this example; the VBA code for the button in alone is all you need.
It's working awesomely in my Excel 2003 (and most likely 2010), but does not work on Excel for Mac due to missing XMLHTTP libraries. I think it should work with pretty much any REST webservice. In my opinion Microsoft would like to have VBA dead and it is pushing dot net solutions for Office.
This example, includes a web service, which returns CEP (code postal) according to the address you request. Actually, user can find any Address here in Brazil, for instance, when a user types a street, avenue starting with letter B, web service will return all data address starting with B, in all over Brazil, this is really big result. My database has more than 200 megabytes. Actually, this database contain more than 900 thousands records, 45,738 districts and 10,698 places.
My mormot_excel.xls sheet accesses the web service and displays the data provided by the webservice. Clicking on the clear button clears the data from the Excel sheet).
The REST HTTP version is both simpler and more portable, you can create this with Android platform and demands less code on the server side as well. Why wouldn't you use REST rather than SOAP throughout your work? If you control both ends of the transaction, this is a very appealing option, as it lets you use whatever web tools you like, not just tools specifically oriented toward SOAP web services. However, there are many services that are available only through SOAP, and a growing number of programmers who know how to work with SOAP. It's best to have both approaches in your toolbox. Now our little mORMot supports authentication using Delphi, javascript, java-android and visual basic for applications. This is very nice, isn’t it?
Tags: Remote service with Excel; webservice with Excel; CEP with Excel.
