As someone who started of programming with Excel VBA, I always follow the developments within the Office space with a keen interest so when we received a number of requests for our Excel Add-ins for Oracle database this week, thought I’d share my experiences and what’s been happening in the world of Office Development.
At work, we mainly distribute these Add-ins to users who have limited knowledge of SQL or as a means to limit the amount of access granted. One of the benefit of these Add-ins is it acts like a mini self-service portal similar to SSRS except your source is not limited to SQL Server-you can point to anything including various vendors (Oracle, Teradata, etc) and APIs (REST, SOAP, NoSQL, Cloud, etc.) and consolidate all this in a multithreaded engine without the need of additional servers and the associated maintenance and costs. So it’s a .Net application with grids.
While most people use Office every day, I’d say the vast majority are probably not aware of the tremendous changes to Office since v2013 from a programming/extensibility perspective and what can be done on Office nowadays.
Prior to Office 2013, you would program Office largely in VBA and XML or if you needed something more, .Net (C# or VB.Net) and XML.
XML is for the user interface-the way things appear. With some XML programming, you can get your Ribbons or Task Panes to look like this below (by the way XML referred to below was dynamically loaded based on the results of a SQL query from a database). While dynamic in creation, the form it takes is still static and so it lacks the interactivity/flash you get with web pages and so you’re stuck with something that looks dull.
With the release of Office 2013, things have just changed, considerably- so much so if you open the template up for Office Development, you’d think you were working on a web site project in ASP.Net. This is what programming in Excel/Office looks like in 2017.
What can you do with all this? Well, it’s up to your imagination and skills of your developer really but to get a feel of things, you could go to the Apps Store for Office or better still, visit the Developer Centre and download some samples to play with-some are pretty sophisticated and cutting edge. This demo below that I downloaded connects to OData in the clouds.
Have a quick look at this video of what I quickly whipped up for testing- You may not be impressed by my web programming or video editing skills from watching it but you should be impress by what Office can now offer or at least get an idea of what I’m talking about 😊 .
I tried this two years ago and gave up as Office 2013 was a bit buggy and the library seemed half bake (aren’t they always with version one). I tried this again recently when I upgraded to Office 2016, much better experience.
Final note, it’s not just all bells and whistles either, professional development for Office for a start means your computer doesn’t freeze on any operations that takes more than a few seconds like it does with VBA-you have the full power of .Net and now with the Office Web Apps, you also have integrated web technology as well at your disposal. A macro won’t let you do things like upload or run queries against Googles Analytics, Azure Data Lakes or Amazon Web Services, nor does it have the capability to communicate with the more established REST/SOAP APIs that’s already in your organisation today.
Pity work is still on 2013…we’ll have to wait.. Just not sure I want all that extra competition though, my dynamic XML suddenly looks very ordinary next to these guys.