In Excel 2007 it’s possible to add controls, referenced by a qualified ID (idQ attribute), to the Quick Access Toolbar, and in Excel 2010, to add controls to the Quick Acces Toolbar, or the ribbon. Furthermore, it is possible to add and/or position custom controls on or next to the Analysis tab, or groups on the Analysis tab, by using attributes like insertBeforeQ and insertAfterQ. The approaches for configuring the Ribbon are beyond the scope of this article – See here and here for an overview.
This article provides a summary of the qualified control IDs (idQ attributes) for the Analysis tab, and the groups on the Analysis tab. They’re qualified by namespace: “SBO”, but you can use a namespace of your own choosing.
The Analysis Tab
idQ=”SBO:com.sap.ip.bi.analysis.menu”
The Analysis Groups
Data Source:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.dataconnections”
Undo:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.backandforward”
Data Analysis:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.dataanalysis”
Display:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.display”
Insert Component:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.insertGroup”
Tools:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.tools”
Planning:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.planning”
Design Panel:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.Taskpanel”
Settings:
idQ=”SBO:com.sap.ip.bi.newpioneer.excel.setting”
Hi, Andrew,
Thank you for a big number of useful tips!
If you have also found idQ-s for buttons, I would be very grateful.
Ksenia
Hi Ksenia,
Sorry for the late reply. I’m not sure which buttons you need the idQ-s for?
I’ll try to put a more complete list together, but here are 3 of the most commonly used buttons:
If you have a need for a particular idQ, please let me know.
Refresh All: idQ=com.sap.ip.bi.pioneer.core.command.refresh
Prompts: idQ=com.sap.ip.bi.pioneer.core.command.refreshWithPrompt
Show/Hide Design Panel: idQ=com.sap.ip.bi.newpioneer.excel.displaytaskpanel
Hi, Andrew!
Thank you for sharing, that was exactly what I needed 🙂
Hi Andrew,
Currently i have a requirement to disable built-in prompts button in data analysis group in Analysis Tab or make all variables displayed in the prompts as Read only.
Is any of the above approach is possible?
Also i got the idQ of Prompts and Data Analysis from your above reply.
But i tried with below code to make the visibility of Prompts as False but it dint work. Could you please let me know what went wrong?
Also let me know if there is any other approach to disable the built-in button in Analysis office.
Best Regards,
Dhanraj
Hey Andrew,
I’m currently using the UI Editor to add a new tab, which should include different ribbons (own created ones and Analysis specific ones).
My coding looks like this:
And it displays this:
My goal is to use the group “Planning” from the Analysis tab within my own tab “Planning functions”.
Am I doing it wrong? Can you help me?
Thanks in advance
Kind regards
Dominik
Hi Andrew,
thank you for this great article.
I have not found another comprehensive list of Analysis controls to be used in a custom ribbon.
I would like to use the “Save data” button of the planning group (without the other plannning buttons).
Can you provide this ID-Q, too?
Any hint is very much aprechiated!
Thanks,
Leo
Hey Leo,
I can share some simple VBA solution with you. We have used the UI editor to create a customized ribbon. Afterwards we have assigned Analysis functions via VBA. You can find the required codings in the Analysis for Office User Guide.
Our customized buttons look like this:
The VBA coding is really simple:
Kind regards
Dominik
Hi Dominik,
thanks for your input. This is a good solution.
In the meantime, I have found the control id for the “Save data” button:
idQ=”com.sap.ip.pioneer.core.command.savePlanValues”
It works just like the Button/VBA you implemented, with the difference of being “greyed-out” whenever there is nothing to save.
Best regards,
Leo