Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Excel 2007 FileFormat > Add RibbonX

The New Excel 2007 File Format

Adding RibbonX code to an Office OpenXML file using VBA

This article has also been published on Microsoft's MSDN site:

http://msdn.microsoft.com/en-us/library/dd819387.aspx

On the previous page I showed how to access and modify existing parts of an Office OpenXML package. This opens up the path for us to add ribbon customisation code to an Office file. For this to happen, a couple of modifications were needed to the code in the class module I showed earlier. Fellow Excel MVP Ken Puls was kind enough to make some modifications to the class module, which I refined a little. The results are summarised below.

Download

I have made the file used in this article available for download:

EditOpenXML.zip

Modifications to the class module

The class module needed some additions to handle adding CustomUI code. One of them is a routine that edits the relatationships (.rels) file in the folder "_rels" to add a reference to a newly inserted customUI folder. This code edits the .rels file by adding the proper relationship:

Public Sub AddCustomUIToRels()
'Date Created : 5/14/2009 23:29
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Add the customUI relationship to the rels file

    Dim oXMLDoc As MSXML2.DOMDocument
    '    Dim oXMLElement As MSXML2.IXMLDOMElement
    Dim oXMLElement As MSXML2.IXMLDOMNode
    Dim oXMLAttrib As MSXML2.IXMLDOMAttribute
    Dim oNamedNodeMap As MSXML2.IXMLDOMNamedNodeMap
    Dim oXMLRelsList As MSXML2.IXMLDOMNodeList
    'Create a new XML document
    Set oXMLDoc = New MSXML2.DOMDocument
    'Attach to the root element of the .rels file
    oXMLDoc.Load XMLFolder(XMLFolder_rels) & ".rels"

    'Create a new relationship element in the .rels file
    Set oXMLElement = oXMLDoc.createNode(1, "Relationship", _
                                         "http://schemas.openxmlformats.org/package/2006/relationships")
    Set oNamedNodeMap = oXMLElement.Attributes

    'Create ID attribute for the element
    Set oXMLAttrib = oXMLDoc.createAttribute("Id")
    oXMLAttrib.NodeValue = "cuID"
    oNamedNodeMap.setNamedItem oXMLAttrib

    'Create Type attribute for the element
    Set oXMLAttrib = oXMLDoc.createAttribute("Type")
    oXMLAttrib.NodeValue = "http://schemas.microsoft.com/office/2006/relationships/ui/extensibility"
    oNamedNodeMap.setNamedItem oXMLAttrib

    'Create Target element for the attribute
    Set oXMLAttrib = oXMLDoc.createAttribute("Target")
    oXMLAttrib.NodeValue = "customUI/customUI.xml"
    oNamedNodeMap.setNamedItem oXMLAttrib

    'Now insert the new node at the proper location
    Set oXMLRelsList = oXMLDoc.SelectNodes("/Relationships")
    oXMLRelsList.Item(0).appendChild oXMLElement
    'Save the .rels file
    oXMLDoc.Save XMLFolder(XMLFolder_rels) & ".rels"

    Set oXMLAttrib = Nothing
    Set oXMLElement = Nothing
    Set oXMLDoc = Nothing
End Sub

Additionally I modified the code that writes XML to a file so it detects when you're trying to add customUI to the file in question. If so, it checks if the customUI folder already exists and if not, it adds it and subsequently updates the aforementioned .rels file:

Public Sub WriteXML2File(sXML As String, sFileName As String, sXMLFolder As XMLFolder)
'-------------------------------------------------------------------------
' Procedure : WriteXML2File
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 6-5-2009
' Purpose   : Writes sXML to sFileName
'             Modified by Ken Puls 2009-05-12
'             Adjusted to add ability to write to customUI container
'-------------------------------------------------------------------------
    Dim oXMLDoc As MSXML2.DOMDocument
    Set oXMLDoc = New MSXML2.DOMDocument

    'If attempting to write a customUI component, test to see if one exists
   
    'Should probably test the .rels file to see if the CustomUI relationship exists...
    If sXMLFolder = XMLFolder_customUI Then
        If Not FolderExists(XMLFolder(XMLFolder_customUI)) Then

            MkDir XMLFolder(XMLFolder_customUI)
            'Write the XML to the file
            oXMLDoc.loadXML sXML
            oXMLDoc.Save XMLFolder(sXMLFolder) & sFileName
            'CustomUI has not been created yet.  Rels file needs to be adjusted
            AddCustomUIToRels
        End If
    End If

    'Write the XML to the file
    oXMLDoc.loadXML sXML
    oXMLDoc.Save XMLFolder(sXMLFolder) & sFileName
End Sub

How to add Custom UI

There is a small demo routine that shows how customUI code is added to a file. The code below demonstrates what simplicity we got by using a class module to take care of the dirty work for us:

Public Sub DemoWritingRibbonXML2File()
'-------------------------------------------------------------------------
' Procedure : Demo
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 06-05-2009
' Purpose   : Demonstrates Writing RibbonX code to an Office Open XML package
'-------------------------------------------------------------------------
    Dim cEditOpenXML As clsEditOpenXML
    Dim sXML As String
   
    Set cEditOpenXML = New clsEditOpenXML
   
    With cEditOpenXML
        'Tell it which OpenXML file to process
        .SourceFile = ThisWorkbook.Path & "\formcontrols.xlsm"
       
        'Before you can access info in the file, it must be unzipped
        .UnzipFile
       
        'This is the RibbonX code we want to write to the file
       sXML = "<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & _
       "<ribbon startFromScratch=""false"">" & _
       "<tabs>" & _
       "<tab id=""customTab"" label=""Custom Tab"">" & _
       "<group id=""customGroup"" label=""Custom Group"">" & _
       "<button id=""customButton"" label=""Custom Button"" imageMso=""HappyFace"" size=""large"" onAction=""Callback"" />" & _
       "</group>" & _
       "</tab>" & _
       "</tabs>" & _
       "</ribbon>" & _
       "</customUI>"
       
        'Now write the xml to the file
        '(the class takes care of the relationships for us):

        .WriteXML2File sXML, "customUI.xml", XMLFolder_customUI
       
        'Now rezip the unzipped package
        .ZipAllFilesInFolder
    End With
   
    'Only when you let the class go out of scope the zip file's
    '.zip extension is removed

    'in the terminate event of the class.
    'Then the OpenXML file has its original filename back.
    Set cEditOpenXML = Nothing
End Sub

Conclusion

The code shown in this article and in the associated download file shows you a way to add RibbonX customisation to an Office 2007 OpenXML file using VBA. This enables us to update ribbonX code inside an existing Excel file on-the-fly, which is normally impossible.


 


Comments

Showing last 8 comments of 20 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (10/7/2009 7:15:05 AM)

Hi Andy,

The clsEditOpenXML is the name of the class module

The best you can do, is download the sample file (see Download section above). In that file, all required definitions have been implemented.

The file works, so you need not change anything to make it work.

 


Comment by: Costas (4/4/2011 1:01:38 AM)

Hi! I created a custom ribbon that included a text button. How can I get the text entered to appear in a specific worksheet cell address. Thanks.

 


Comment by: Jan Karel Pieterse (4/4/2011 3:51:34 AM)

Hi Costas,

Not sure what your goal is? Do you want some text to appear in a cell when the user clicks your ribbon button?

If so, then you have to include the 'onAction' attribute in the RibbonX XML for that button and make sure there is a callback VBA macro with the same name in the VBA project of the file with the ribbon customisations.

 


Comment by: Pascal (6/13/2011 7:07:20 AM)

Ik heb het maar zo gedaan:

http://social.msdn.microsoft.com/Forums/en/oxmlsdk/thread/70025c07-ae4b-4f23-8ad6-702ed09a1145

 


Comment by: Jon Peltier (4/2/2013 11:35:31 PM)

Is there a problem having both an enum named XMLFolder and a property named XMLFolder in the class module? I'm confused, and I can just imaging the VB compiler...

 


Comment by: Jan Karel Pieterse (4/3/2013 9:26:48 AM)

Hi Jon,

To be honest, I don't know. I do use this code in a utility without a problem, so I wouldn't expect it to be?

 


Comment by: MK (11/18/2013 3:09:37 PM)

Hi Jan

I am really interested in what you achieved. I unloaded both the files in the zipped folder, but am at a loss what to look for and how to use the 2 files. Pl forgive my ignorance, but can you pl guide me on how to use this feature?

Thanks.

 


Comment by: Jan Karel Pieterse (11/18/2013 4:43:13 PM)

Hi MK,

I've contacted you by email to discuss this.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].