Category Archives: 13294

Deploy Me Simple


Excel 2003 Addins In Excel 2007


Excel 2007 and 2010 is so different in concept to Excel 2003 and before that there are many new challenges in providing solutions that can be deployed in either version.


One challenge that I have been faced with is creating addins that may be used in Excel 2007 or earlier versions. My addins would usually have a menu and/or a toolbar to provide access to the functionality within the addin. In previous versions of Excel, this has been an elegant solution as most people are familiar with the menus and are easily able to find the new functions in the Excel 2003 UI.


As with many things, Excel 2007 changed all of that. Excel 2007 supports the old commandbar model, but in a particularly inelegant way. The menus and toolbars that an addin might create are dropped onto the Add-Ins tab. Figure 1 shows my Excel 2007, with two addins installed, NameManager1 and Snagit2



Figure 1 – Addins Tab



Figure 2 – NameManager Menu


As I said, this is not an elegant solution, far better to adopt the Ribbon paradigm and either add a ribbon tab, or add a group to an existing tab.


The problem here is that whilst Excel 2007 can handle commandbars, Excel 2003 doesn’t understand the ribbon. The obvious solution is to have two versions of your addin, one that is Excel 203 compliant, one for Excel 2007.  This is far from ideal, having to maintain two versions where 90+% of the code will be the same is a maintenance nightmare. Another alternative is to put all of the common code into a DLL, which would still be multiple files and might entail a large amount of work re-writing the code to manage an Excel instance.


The solution that I am proposing here is to have two addins, but two very different addins.


Overall Design


I will be using one of my addins in the discussion that follows. This addin, called ‘SP Builder3, is a small addin with a simple menu that will usefully demonstrate this technique without confusing the matter with too much detail.  The 2003 menu for ‘SP Builder’ is shown in Figure 3



 


Figure 3 – SP Builder Menu


Figure 4 shows the ribbon version of ‘SP Builder’. As you can see, I have added a three button group to the Developer tab in the ribbon.



Figure 4 – SP Builder Ribbon


The functionality of each addin is as follows:-


  • Excel 2003 – functionally unchanged, apart from modifying the workbook open code to test the Excel version, if it is 2003 or lower the commandbars are loaded, if not the Excel 2007 workbook is opened. The workbook close code deletes the commandbars or closes the Excel 2007 addin
  • Excel 2007 – a basic addin that has ribbon XML. The callback code invokes the actual procedures in the Excel 2003 addin. When this addin loads, the ribbon is automatically built, and, because the Excel 2003 addin checks the version, the coomandbars are not loaded so we have nothing in the Add-Ins tab.

Excel 2003 Addin


As mentioned above, the only changes to the Excel 2003 addin are the open and close routines.


The workbook open checks the current Excel version and either creates the commandbars, or loads the Excel 2007 workbook, depending upon the version, as shown in Figure 5.


     If Val(Application.Version) < 12 Then

         If Not mhCommandBars.CreateMenu(False) Then Err.Raise appErrorGeneric
     Else

         Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & WB_RIBBON
     End If


 


Figure 5 – Addin Initialisation


Similarly, the workbook close changes are shown in Figure 6.


     If Val(Application.Version)< 12 Then

         Call mhCommandBars.DeleteMenu
     Else

         Workbooks(WB_RIBBON).Close SaveChanges:=False
     End If


Figure 6 – Addin Removal


That is all that is required in the Excel 2003 addin, as you can see this adds no maintenance overhead.


Excel 2007 Addin


The Excel 2007 addin is completely new, but again there is nothing difficult or overly complex in this code. It is basically a wrapper to present the ribbon to the user, it has no real functional purpose in itself.


First we will look at the XML required to define the ribbon changes. Figure 7 shows the XML.


The XML here is very straight-forward, a single group with three buttons. It is likely that your addin will have a richer interface which will require more XML but that is not the objective of this article. As you can see, the group is added to the Developer tab. This is because I see the SP Builder addin as a development tool, but your addin could be in any tab, or even add another tab to the ribbon. One other thing to note is that all of the buttons call the same callback routine, this is explained later.


<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui
          onLoad=rxspRibbonOnLoad>

  <ribbon startFromScratch=false>

    <tabs>

      <tab idMso=TabDeveloper>

        <group id=grpSPBuilder label=SP Builder>

 
          <button id=btnSPBuilder
                  label=Build SPs
                  imageMso=ControlsGallery
                  size=large
                  onAction=rxspOnAction />
 
          <button id=btnSPList
                  label=List SPs
                  imageMso=ConditionalFormattingHighlightDuplicateValues
                  size=large
                  onAction=rxspOnAction />
 
          <button id=btnSPAbout
                  label=About…
                  imageMso=Info
                  size=”large”
                  onAction=”rxspOnAction />
        </group>
      </tab>
   </tabs>
  </ribbon>
</customUI>


Figure 7 – Ribbon XML


In my Excel 2007 ribbon handling code, I always create a set of constants for the ids of each of the controls, as shown in Figure 8, which are then used in the callback code.


Option Explicit

Global Const CONTROL_ID_BUILD As String = “btnSPBuilder”
Global Const CONTROL_ID_LIST As String = “btnSPList”
Global Const CONTROL_ID_ABOUT As String = “btnSPAbout”


Figure 8 – Ribbon Constants


The final piece of this jigsaw is the ribbon callback code, Figure 9. As said earlier, I use a single callback and test the control id to determine the action to take. The key aspect of this code is that the action for each of the buttons is to run the ‘real’ procedure in the Excel 2003 addin, which is achieved with Application.Run4.


Option Explicit

Global rxspIRibbon As IRibbonUI

Public Sub rxspOnAction(control As IRibbonControl)
    Select Case control.ID

        Case CONTROL_ID_BUILD:  Application.Run “‘SP Builder.xla’!SPBuild”

        Case CONTROL_ID_LIST:   Application.Run “‘SP Builder.xla’!SPList”

        Case CONTROL_ID_ABOUT:  Application.Run “‘SP Builder.xla’!AboutSPBuilder”5
    End Select
End Sub

Public Sub rxspRibbonOnLoad(ribbon As IRibbonUI)
    Set rxspIRibbon = ribbon
End Sub


Figure 9 – Ribbon Callback Code


Summary


This provides a simple, and elegant, way to manage addins that are required to be deployed in Excel 2007 or earlier versions.


By using a ribbon wrapper to deliver the ribbon changes, we are keeping the code to a minimum, and it is not introducing a maintenance overhead.



1There is a 2007 version of NameManager which I believe has a ribbon, but I have not installed it

2 Interestingly, Snagit has not added to the ribbon although the product has adopted the Ribbon UI!

3 Although t is not material to this discussion, ‘SP Builder’ is a small addin that simplifies building stored procedures and loading them into a database

4 Although you cannot pass parameters directly in a callback, this technique also allows passing a paremeter to the 2003 procedure. This could be useful to let the ‘worker’ addin know that you are running from Excel 2007 if it wanted to do something differently in that instance

5 It would be a far better practice to create global constants with the called addin and procedure details rather that repeating this in hardcoded form, similar to how I manage the control Ids

The XML Is On The Kid















I recently wrote about a database tool that I have built to facilitate adding stored procedures to databases, SP Builder.


 


XL-Dennis made a couple of comments, one of which was with regards to the medium used for the script file. He talked about XML files, and whilst I had used an INI file when I originally built it I had thought about using XML but dismissed it at the time as my XML skills and knowledge were minimal to put it mildly (oddly, I have been aware of and using XML for over 12 years now, mainly as a consumer of such, but I still feel it promises far more than it delivers as a technology).


 


XL-Dennis’ comment gave me the prod I needed to actually do something about this, so I set about converting my tool to using XML files.


 


This is the structure of the XML I designed, using the same data as my previous posts.


 


<?xml version=”1.0″ encoding=”utf-8″?>
<database type=”Access”
         path=”C:\MyDatabases\”
         name=”MyDB.mdb”>

   <category name=”Get”>
 
       <procedure name=”spGetCompanyGoals”>
       
           <SQL code=”SELECT SUM(RD.SalesGoal) AS ‘Company Sales Goal’, ” />
           <SQL code=”      SUM(RD.BonusGoal) AS ‘Company Bonus Goal’ ” />
           <SQL code=”FROM refUsers AS RU ” />
           <SQL code=”WHERE LoginID = prmLoginId;” />
       </procedure>
 
       … more procedures
   </category>
 
   <category name=”Delete”>
 
        <procedure name=”spDeleteSalesType”>
 
           <parameter name=”prmUpdatedBy”
                      type=”VarChar (50)” />
 
           <parameter name=”prmSalesTypeID”
                      type=”Integer” />
           
           <SQL code=”UPDATE  refSalesTypes               ” />
           <SQL code=”SET     Deleted = TRUE,             ” />
           <SQL code=”        UpdatedBy = prmUpdatedBy,   ” />
           <SQL code=”        UpdatedOn = Now>             ” />
           <SQL code=”WHERE   SalesTypeID = prmSalesTypeID;” />
       </procedure>
 
       … more procedures
 
       … more categories
   </category>
</database>


 


There are some distinct advantages to the XML to my mind. These are:


  • it is more readable
  • there is less meta-information, such as the various counts, because the XML parser will provide all of that to the code
  • the XML parser provides a level of validatio
  • tools such as Altova XMLSpy provide a much better IDE for creating and updating these files than a text editor, as well as validation

 


There are some disadvantages of course, but the only one that really irks me is having to use &amp; for & and so on. I understand why I have to, but it still rankles a tad.


 


In the XML design, there were a couple of decisions to be made, around the elements and attributes. Before I embarked upon the design I read the chapter on XML in the second edition of the indispensable Professional Excel Development, and whilst it suggested encapsulating the data in elements rather than attributes I chose to use attributes as I felt it was more readable that way, attributes still need to be within a parent element which loses clarity. In my mind, this


 


           <parameter name=”prmSalesTypeID”
                      type=”Integer” />


 


is far simpler than this


 


           <parameter>
               <name>prmSalesTypeID</name>
               <type>Integer</type>
           </parameter>


 


Changing the addin was trivial once I had worked out the code for reading the XML file, updating the INI file to my XML format took far more time. The XML version ofthe addin can de downloaded here.


 

 


SP Builder is supplied as an Excel 2003 addin, or XLA file, and adds a menu option to the Tools menu with three options, Build SPs, SP Builder List and About.





 


As you many know, when Excel 2003 workbooks that create commandbars are opened in Excel 2007 or 2010 the menus are accessed via the Addins tab. This is not a satisfactory solution, so the addin tests the Excel version, and if it is 2003 or earlier it builds the commandbars, it is 2007 or 2010 it loads a ‘light’ 2007/2010 addin that adds a group to the Developer tab.



 





This way, we have a single addin that runs in any version of Excel.


 


 


One thing that I found was that I could not insert comments in my file, the parser failed when I had comments. I used what I believe is the correct format for comments, that is


<!—
‘———————–
‘ Check Stored Procedures
‘———————–
–>


but it only worked when I completely stripped out the comments.


 


I also created an XSD file for the XML, which I used to play with reading it into Excel, but I see no real use for Excel in this process other than hosting the code, so that is going nowhere. Is there any other use I can use the XSD for? This also points at the next step, take Excel out of the process completely and create a standalone VB application; that would make a nice candidate for me to develop some more .Net skills.