The Excel File Format

Pages in this article

Adding RibbonX code to an Office OpenXML file using VBA

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

https://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 (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

All comments about this page:

Comment by: LnddMiles (26-7-2009 15:18:21) deeplink to this comment

Great post! I’ll subscribe right now wth my feedreader software!

Comment by: Subrata Mandal (1-8-2009 09:20:22) deeplink to this comment

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 (2-8-2009 03:33:02) deeplink to this comment

Hi Subrata,

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

Comment by: Subrata Mandal (11-8-2009 23:45:56) deeplink to this comment

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 (7-9-2009 09:38:41) deeplink to this comment

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 (7-10-2009 03:26:00) deeplink to this comment

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?

Thanks,

Andy.

Comment by: Jan Karel Pieterse (7-10-2009 03:40:03) deeplink to this comment

Hi Andy,

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

Comment by: Andy Bird (7-10-2009 04:26:30) deeplink to this comment

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?

Thanks,

Andy

Comment by: Jan Karel Pieterse (7-10-2009 05:18:30) deeplink to this comment

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 (www.excelguru.ca)
    '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 (7-10-2009 05:37:50) deeplink to this comment

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.

Cheers,

Andy.

Comment by: Andy Bird (7-10-2009 06:19:06) deeplink to this comment

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

Please would you let me know what this should be?

Thanks,

Andy.

Comment by: Andy Bird (7-10-2009 06:59:29) deeplink to this comment

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?

Cheers,

Andy.

Comment by: Jan Karel Pieterse (7-10-2009 07:15:05) deeplink to this comment

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 01:01:38) deeplink to this comment

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 03:51:34) deeplink to this comment

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 (13-6-2011 07:07:20) deeplink to this comment

Ik heb het maar zo gedaan:

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

Comment by: Jon Peltier (2-4-2013 23:35:31) deeplink to this comment

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 (3-4-2013 09:26:48) deeplink to this comment

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 (18-11-2013 15:09:37) deeplink to this comment

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 (18-11-2013 16:43:13) deeplink to this comment

Hi MK,

I've contacted you by email to discuss this.

Comment by: Steve Stretch (17-3-2015 11:04:27) deeplink to this comment

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 (17-3-2015 11:36:55) deeplink to this comment

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 :-)

Comment by: Lisa Green (27-10-2019 14:52:00) deeplink to this comment

Hi Jan Karel!!

The download link https://jkp-ads.com/errorpage.asp?loc=https://www.jkp-ads.com/downloads/editopenxml003.zip gives a 404.

Regards
Lisa

Comment by: Jan Karel Pieterse (28-10-2019 09:25:00) deeplink to this comment

Hi Lisa,

The link seems to work just fine for me. The correct link is:

https://jkp-ads.com/DownloadScript.asp?filename=EditOpenXML.zip

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.