Most Valuable Professional

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

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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:

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.


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

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 (
'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", _
    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 = ""
    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
        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 (
' 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
        'This is the RibbonX code we want to write to the file
       sXML = "<customUI xmlns="""">" & _
       "<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>" & _
        '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
    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


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.



All comments about this page:

Comment by: LnddMiles (7/26/2009 3:18:21 PM)

Great post! Ill subscribe right now wth my feedreader software!


Comment by: Subrata Mandal (8/1/2009 9:20:22 AM)

how to Connect visualbasic with 2007 msaccess.accdb?

1) which preference we are use? which connectiviti use?
EX: ADODB.Connection
at first we are used. microsoft activx common control 2.6. now

which defind?


Comment by: Jan Karel Pieterse (8/2/2009 3:33:02 AM)

Hi Subrata,

Record a macro while you setup a connection to the database. That will give you the connection string.


Comment by: Subrata Mandal (8/11/2009 11:45:56 PM)

how to connection. Visual Basic 6.0 to office 2007 MsAccess.accdb ?

how to relationship visual basic 6.0 to Msaccess.accdb ?


Comment by: Jan Karel Pieterse (9/7/2009 9:38:41 AM)

Hi Subrata,

Record a macro in Excel whilst setting up an external data connection, that should give you the proper connection string and command text.


Comment by: Andy Bird (10/7/2009 3:26:00 AM)

Hi, the variable type "XMLFolder" is not recognised when compiling "Public Sub WriteXML2File(sXML As String, sFileName As String, sXMLFolder As XMLFolder)" in VBA.

How did you get around this?




Comment by: Jan Karel Pieterse (10/7/2009 3:40:03 AM)

Hi Andy,

In your VBA project, add a reference to the Microsoft XML v3.0 library.


Comment by: Andy Bird (10/7/2009 4:26:30 AM)

Hi Jan,

I tried that, but it still did not work (I was using MSXML v6.0 library prior to that). The compiler says the user-defined type is not defined. This can only be "XMLFolder" that is not recognised (i.e. remains in lower case, and not converted to blue text). Maybe there is something else I have missed?




Comment by: Jan Karel Pieterse (10/7/2009 5:18:30 AM)

Hi Andy,

Silly me, at the top of the class, there is an Enum declaration:

Public Enum XMLFolder
    'Date Created : 5/12/2009 21:34
    'Author     : Ken Puls (
    'Macro Purpose: Constants for XML Containers
    XMLFolder_root = 1
    XMLFolder_rels = 2
    XMLFolder_xl = 3
    XMLFolder_customUI = 4
    XMLFolder_docProps = 5
End Enum


Comment by: Andy Bird (10/7/2009 5:37:50 AM)

Thanks Jan,

Unfortunately, there is another problem. Public Sub AddCustomUIToRels() doesn't compile because the compiler is not expecting a variable of Enum type in the following line:

oXMLDoc.Load XMLFolder(XMLFolder_rels) & ".rels"

Any further suggestions greatly appreciated.




Comment by: Andy Bird (10/7/2009 6:19:06 AM)

Also, in Public Sub DemoWritingRibbonXML2File(), the user-defined type is not defined: clsEditOpenXML

Please would you let me know what this should be?




Comment by: Andy Bird (10/7/2009 6:59:29 AM)

Hi Jan,

I think I've fixed the first problem in Public Sub AddCustomUIToRels().

I created another variable:
Dim Rels As XMLFolder

I then replaced the following line:
oXMLDoc.Load XMLFolder(XMLFolder_rels) & ".rels"

With these lines:
Rels = XMLFolder_rels
oXMLDoc.Load (Rels & ".rels")

That satisfies the compiler at least. However, "Public Sub DemoWritingRibbonXML2File()" still causes it to bomb out because the "clsEditOpenXML" user defined type is not recognised. Should this also be Enum?




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:


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?



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

Hi MK,

I've contacted you by email to discuss this.


Comment by: Steve Stretch (3/17/2015 11:04:27 AM)

Hi This is great stuff but can it work with Excel 2010/2013 and the customUI14? as I do not seem to be able to get it to work with Excel 2013, what modifications do i need to do?

Thansk in advance


Comment by: Jan Karel Pieterse (3/17/2015 11:36:55 AM)

Hi Steve,

Well, what I would do is first "manually" create a working 2013 file with the customui14 inside. Then figure out what needs to be changed to the code above to make that work. Shouldn't be too hard.

Unfortunately, I don't have time for this right now :-)


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:

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].