Quick access toolbar items disappear after Excel restart

[Felix]

Hi everyone,

we have developed a C# based Excel add-in using Excel-DNA 0.29 and ran into a problem with the quick access toolbar in Excel 2007/2010. The add-in has its own custom ribbon tab (implemented using the ExcelDna.Integration.CustomUI.ExcelRibbon interface). The buttons shown in the ribbon tab can be added to the quick access toolbar, however after restarting Excel they will no longer be shown. 

Excel initially writes the Excel.qat file and references the add-in through its progId, however the ribbon's progId is regenerated every time the add-in is loaded. Thus it will never match the progId in the Excel.qat file. Is there any workaround or proper solution for this?

Thanks for any help!

-Felix

[Govert van Drimmelen]

Hi Felix,

Thanks for reporting this problem. I hadn't realised that the Ribbon
ProgID is used in this way.

Investigating, it opens up a bit of a minefield. I'm haven't really
found a workaround yet.

This article is helpful:
http://www.excelguru.ca/blog/2007/03/19/sharing-a-custom-ribbon-tab-among-workbooks/

And in particular Andrei from Add-in Express had a very informative
comment.

So:
What is actually stored in the .qat is the namespace for the item you
have added to the QAT.
By default, as you say, the namespace is the ProgId for the ribbon
class, which in Excel-DNA currently changes at runtime.
(We had a similar issue for the RTD servers, and that is mostly
resolved in v. 0.29 by allowing you to set up a fixed ProgId.)

However, you can also override the workspace of a ribbon item by using
an idQ='x:MyButton' id instead of id='MyButton'.
The namespace is set in your CustomUI:
<customUI xmlns='http://schemas.microsoft.com/office/2006/01/
customui' loadImage='LoadImage' xmlns:x='My.NameSpace'>

If you do this, then add the button to the QAT, Excel will store the
entry in the .qat file as My.Namespace, and it will recover it right
when restarting.

But there is a problem with this plan, which is what Andrei explained.
The callbacks to your class don't work if the class has a different
ProgId than the namespace. So by changing the namespace for your
customUI ids, you break the ribbon callbacks. I'm not sure if there is
a further workaround. So that doesn't really help us.

At runtime you would be able to change the ribbon .xml returned to
Excel, and fill in the new (dynamic) progId. I'm not sure how this
could help...

The real fix would be to use whatever [ProgId(...)] you set for your
Ribbon class, and for Excel-DNA to use that ProgId. This is already in
place for RTD servers and the custom COM Server classes, I just can't
see immediately whether it will work for ExcelRibbons too. Maybe if
you mark the ExcelRibbon-derived class with [ExcelComClass]? Maybe if
your ExcelRibbon-derived class also implements IRtdServer? If you
search through the COM Server posts in this group, you'll see how the
ComServer.RegisterServer... stuff can register a class with the right
ProgId. That might work for you already, I'm not sure yet.

I'll let you know more, after I've had a chance to experiment a bit
more.

Regards,
Govert

[Felix]

Hi Govert,

I managed to get it to work by setting a custom GUID attribute for my 

CustomUI.ExcelRibbon implementation and changing the LoadComAddIn() 

method of the ExcelComAddInHelper a little bit.

I changed the line

// We pick a new Guid as ClassId for this add-in.

clsId = Guid.NewGuid();

to

CLSID clsId = addIn.GetType().GUID;

if (clsId == null)

{

     // We pick a new Guid as ClassId for this add-in.

     clsId = Guid.NewGuid();

}

and now the QAT items show up after restarting Excel, as expected. I'm not sure

whether this is the proper way to do this or whether it may have any side effects, 

though it appears to be the least invasive solution.

Regards,

-Felix