none
QAT changes lost when using XML to modify Ribbon (Excel 2016 / 2016)? RRS feed

  • Question

  • Using "http://schemas.microsoft.com/office/2009/07/customui" to modify Ribbon (in Excel 2013 / 2016) I discovered that the changes / additions I had manually added to the Quick Access Toolbar in the past had disappeared despite the XML script including a "<mso:qat/> tag.

    The XML scripts that are incorporated in VBA macros were lifted from StackOverflow then modified- 

    Private Sub Workbook_Activate()
    
    Dim hFile As Long
    Dim path As String, fileName As String, ribbonXML As String, user As String
    
    hFile = FreeFile
    user = Environ("Username")
    path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    fileName = "Excel.officeUI"
    
    ribbonXML = "<mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>" & vbNewLine
    ribbonXML = ribbonXML + "  <mso:ribbon>" & vbNewLine
    ribbonXML = ribbonXML + "    <mso:qat/>" & vbNewLine
    ribbonXML = ribbonXML + "    <mso:tabs>" & vbNewLine
    ribbonXML = ribbonXML + "      <mso:tab id='reportTab' label='My Actions' insertBeforeQ='mso:TabFormat'>" & vbNewLine
    ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Reports' autoScale='true'>" & vbNewLine
    ribbonXML = ribbonXML + "          <mso:button id='runReport' label='Trim' " & vbNewLine
    ribbonXML = ribbonXML + "imageMso='AppointmentColor3'      onAction='TrimSelection'/>" & vbNewLine
    ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
    ribbonXML = ribbonXML + "      </mso:tab>" & vbNewLine
    ribbonXML = ribbonXML + "    </mso:tabs>" & vbNewLine
    ribbonXML = ribbonXML + "  </mso:ribbon>" & vbNewLine
    ribbonXML = ribbonXML + "</mso:customUI>"
    
    ribbonXML = Replace(ribbonXML, """", "")
    
    Open path & fileName For Output Access Write As hFile
    Print #hFile, ribbonXML
    Close hFile
    
    End Sub
    
    Private Sub Workbook_Deactivate()
    
    Dim hFile As Long
    Dim path As String, fileName As String, ribbonXML As String, user As String
    
    hFile = FreeFile
    user = Environ("Username")
    path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    fileName = "Excel.officeUI"
    
    ribbonXML = "<mso:customUI           xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
    "<mso:ribbon></mso:ribbon></mso:customUI>"
    
    Open path & fileName For Output Access Write As hFile
    Print #hFile, ribbonXML
    Close hFile
    
    End Sub

    Is it possible to protect / retain the current setup of the QAT whilst modifying the (Excel) Ribbon?

    All contributions gratefully accepted.

    PaulJ

    Friday, October 27, 2017 10:20 AM

Answers

  • Hi Deepak,

    Thank you for your detailed response.

    Having looked into VSTO I can see the potential it might offer.

    Regrettably I only have Visual Studio Community 2015 to work with and it seems not to support VSTO.

    Thanks again for your help - but I have decided to seek another development strategy for my small local business application.

    Rgds,

    PaulJ 

    Tuesday, October 31, 2017 10:53 AM

All replies

  • Hi PJ at StertilUK,

    I try to test the code above on my Excel 2016.

    I find that in Workbook_deactivate event you are modifying the ribbon.

    which also resets the QAT.

    because of this reason it only shows you the default options and remove the other options that you had added.

    so try to add code like below to your xml.

    so when you open the file it also add the options in QAT and Ribbons.

    ribbonXML = "<mso:customUI           xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
     "<mso:ribbon startFromScratch =""true"">" & _
    "    <qat>" & _
    "      <sharedControls>" & _
    "        <control idMso=""FileSave"" screentip=""Išsaugoti dokumento pakeitimus (Ctrl+S)""/>" & _
    "        <control idMso=""Undo"" screentip=""Anuliuoti paskutini veiksma (Ctrl+Z)""/>" & _
    "        <control idMso=""Redo"" screentip=""Atstatyti i paskutini veiksma (Ctrl+Y)""/>" & _
    "        <control idMso=""FilePrintQuick"" screentip=""Greitas spausdinimas""/>" & _
    "        <control idMso=""FilePrintPreview"" screentip=""Spausdinimo peržiura"" />" & _
    "        <control idMso=""MoreControlsDialog"" screentip=""More Commands..""/><sharedControls><qat>" & _
    "</mso:ribbon></mso:customUI>"

    modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 30, 2017 2:30 AM
    Moderator
  • Thanks for your response Deepak - it looks like something I may be able to work with (but probably without the Lithuanian screentips!).

    However, the Excel application I want to construct is for publication amongst my work colleagues and I have no way of knowing which commands they may have individually added to their QAT.

    Isn't there a way to "ignore" the QAT when editing the the Ribbon (i.e. leave the QAT commands as they are)?

    Paul J

    Monday, October 30, 2017 9:38 AM
  • Hi PJ at StertilUK,

    as far as , I understand your requirement is to only implement the option in ribbon for one particular workbook.

    so when user try to open the workbook then he only find your customized ribbon in that particular workbook.

    and when he close the workbook then option will get removed so when he use any other workbook then he not get this customized ribbon.

    but you will notice that when user opens multiple workbook at the same time then user will get this customized ribbon in every workbook.

    which may not appropriate as per your requirement.

    other thing is that QAT is the part of the ribbon.

    so if you clear the customization from the ribbon then QAT also will be get reset.

    so here, I think that your approach is not correct to solve this issue.

    if you can use VSTO then you can easily solve this issue.

    in VSTO you can create Document level customization.

    which will only apply to one particular workbook.

    in that you can develop the ribbon and it will only available for that particular workbook.

    so you not need to remove ribbon when you close the workbook.

    options in QAT will remain same and not get affected.

    you can use Same VBA Excel object model with more functionality of object oriented language like C# and VB.

    so if you are available to move to C# or VB.net then you can create VSTO Workbook Project.

    Reference:

    Walkthrough: Creating Your First Document-Level Customization for Excel

    Walkthrough: Creating a Custom Tab by Using Ribbon XML

    Walkthrough: Creating a Custom Tab by Using the Ribbon Designer

    let me know if this approach is suitable to fulfil your requirement or not.

    currently this is the easiest option available to solve this issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 31, 2017 12:59 AM
    Moderator
  • Hi Deepak,

    Thank you for your detailed response.

    Having looked into VSTO I can see the potential it might offer.

    Regrettably I only have Visual Studio Community 2015 to work with and it seems not to support VSTO.

    Thanks again for your help - but I have decided to seek another development strategy for my small local business application.

    Rgds,

    PaulJ 

    Tuesday, October 31, 2017 10:53 AM
  • Hi PJ at StertilUK,

    it's good to know that you found the solution for your issue.

    other thing you had mentioned that,"Regrettably I only have Visual Studio Community 2015 to work with and it seems not to support VSTO."

    Here, I want to inform you that any version of Visual Studio supports VSTO.

    it doesn't matter it is community , enterprise or professional version.

    the thing is that when you installed the Visual Studio, you did not select the Office development components to be installed with Visual Studio.

    and this is the actual reason that you are not able to see any Office development templates in Visual Studio.

    if you want to enable it then you can go to  the control panel.

    then go to  the Programs and Features.

    find the Visual Studio from the list.

    right click on it and select modify.

    check the checkbox of Office development tools and installed it.

    restart your Visual Studio.

    you will see that the Office development templates will be available in the list of new project.

    Reference:

    How can I add the tool for deveopping Office 365 on my visual studio enterprise 2017?

    the link is for VS 2017 but steps will remain same for all other version of Visual Studio too.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 1, 2017 12:28 AM
    Moderator
  • Hi again Deepak,

    I followed your Control Panel instructions to update VS but unfortunately the list of features did not include "the checkbox of Office development tools".

    But many thanks anyway

    PaulJ

    Wednesday, November 1, 2017 8:21 AM
  • Hi PJ at StertilUK,

    you can also download it from link below.

    Office Developer Tools

    Latest Microsoft Office Developer Tools for Visual Studio 2015

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 1, 2017 8:32 AM
    Moderator