Ribbon on the Fly, or the Zip

I recently needed to build an Excel addin that supported a variable number of options, with buttons to invoke the options. That in itself is not unusual, but I also wanted the numbers to be driven by the user; which always complicates matters.

The definition of the buttons is simple, even allowing for the fact that it will be maintained by users. I could use a separate configuration workbook, an INI file, or even XML. As I am looking to make this a user driven facility, within an overall Excel framework, an Excel workbook seems the best option.

For the addin, in Excel 2003, we have a simple solution, namely read the workbook and build a menu of options dynamically on startup. As seems to be the norm, it is not so simple for Excel 2007 and 2010. I could just build commandbars as for Excel 2003 and let them show in the Addins tab, but this is a very poor solution in my view. I showed in a previous post last year how we can manage addins in both Excel 2003 and later versions, and support menus or the ribbon, and it was my intention to use this technique here. This does not cater for the variable options in the ribbon however.

There couple of obvious ways to manage the variable options in the ribbon. One would be to use a dynamic menu control, which can be dynamically populated when the menu is invoked. This is a nice option for lists, such as file lists, but I don’t like it as the only option in my ribbons, it does not seem to fit the ribbon paradigm. Another way would be to define a fixed number of buttons, make the visibility of the buttons a dynamic setting, and use the values in the configuration workbook to determine which are made not visible. Again, this is not an ideal solution in my view, it is almost guaranteed that not enough will be defined.

As I mentioned above, I already have a technique for supporting menus and the ribbon in my addins, and this consists of an Excel 2003 addin which is the primary component that checks the version, builds commandbar menus if the Excel version is less than 12 (Excel 2003 or earlier), otherwise it opens a ribbon wrapper addin. All of the grunt work is done in the Excel 2003 addin, and these procedures are called from the ribbon wrapper addin. The key here for Excel 2007 and on is that the ribbon addin is not initially opened. As Excel 2007 is a zip file with various components, including a CustomUI XML component, it is possible to open this zip file and overwrite the CustomUI XML. So, before I need to open the ribbon addin, I can dynamically update the XML, and I have a fully dynamic ribbon.

Of course, there is still the issue of calling the correct procedure regardless of whether the addin is loaded in Excel 2003 or a ribbon enabled Excel. The issue arise in the ribbon wrapper, the button actions need to call procedures in the Excel 2003 addin, and in my technique I do this using Application.Run calls across projects. Because all of the code in my required addin will perform the same action, just on differing files which are defined in the configuration file, this works fine, but the configuration file could always be extended to provide the name of the project and procedure to be called.

So much or the theory, just need to get on and create it now.

Keep It Simple? Don’t be Daft!

Keep It Simple? Don’t Be Daft!

I was recently working on a project where we were using SSRS to create XML reports which are then imported into Excel to facilitate further analysis.

Unfortunately, I could not see any option in PowerPivot to connect to an XML source. There is the option for text and RSS, but no XML that I can see. I know I could create an RDL and connect to that, but XML is far more than just SSRS.

I raised this with some colleagues, and one of them pointed me to this Microsoft article, Data Feed XML Syntax. I came away shell-shocked from reading this article. I cannot understand Microsoft’s reasoning here. I thought that RSS used XML as its transport protocol because it (XML) was a recognised data transport standard. RSS may use a particular dialect of XML, that is hardly surprising as it has particular needs, but why would anyone choose to use that dialect for its general XML import? We have a universal standard, and a particular implementation of that standard. So what do Microsoft use in PowerPivot but the RSS specific implementation of XML. Surely, it would have made more sense to build an XML import capability, then modify that for the particular implementation that is RSS.

To create a situation whereby we have write code to change the original XML to conform to some other implementation that is totally irrelevant to our problem is madhouse development. To quote that document … Programmers who use other tools or approaches (such as Microsoft generated XML – my words) will need to know how to structure a valid XML response that can be processed by the data feed reader that is built into the PowerPivot for Excel. This is a long way from my idea of self-service BI.

At times I wonder what planet Microsoft inhabits, it often seems to be a different world to mine. You can (easily) import XML into Excel, so they can do it, have done it. I knew we were in trouble as soon I saw SharePoint mentioned in the article, the bane of Office at the moment is that Microsoft seem intent on subsuming it within SharePoint.

Mr Richard (sorry, I cannot use the name he addresses himself by, this blog sees that as a profanity) Moffatt also makes a an interesting read on Office and SharePoint.