Can't reference Interop Excel CommandBars collection

[Meteor]

Using VS2005, I have written an Excel add-in, firstly using VSTO, then
later, (when I ran into problem after problem), as a shared add-in
using Interop.Excel.

The add-in runs fine on my development machine, however when deployed
to any other machine, it won't run. I commented out all the variable
declarations, and put in MessageBox.Show() alerts to find out where
the code is stopping.

Using that method, I found out that the problem occurs when I try to
reference the CommandBars collection of the Application
(Microsoft.Office.Interop.Excel.Application) object. No amount of try
catch will display any error, and the add-in will just stop running at
that line. I can get a reference to the CommandBars collection using
'object bars = Application.CommandBars as object', but as soon as I
try to do anything with the resulting object, the add-in silently
exits again. If I use
"MessageBox.Show('Application.CommandBars.Count') or something
similar, I have no luck.

I need the reference to the CommandBars collection so that I can add a
new menu item.

Incidentally, as the add-in is loaded on startup, I can't figure out
how you could possibly debug it - you can't attach to the remote Excel
process until after the add-in has failed - can you? Being a COM add-
in, it doesn't appear in the list of available add-ins, so how could
you ever load it after Excel is running?

Can anyone offer a suggestion?

Thanks in advance

 

[Meteor]

Managed to attach the debugger to the remote machine, and reload the
add-in (after adding a "COM Add-Ins..." button to the toolbar and
checking the box).

I can see the line that calls the function containing the deadly code,
but although the function contains a "try catch" block, the code just
skips right over it and quits. If I reference the entire CommandBars
collection into an object variable, I can then examine it to find the
required MenuBar using QuickWatch, but if I try to use anything other
than a simple 'object' reference to the CommandBars collection,
everything dies.

 

[Meteor]

Anyone from Microsoft care to comment on this problem?

 

[Meteor]

Seeing as nobody offered any suggestions or assistance, I'll outline
how I resolved the issue for anyone who comes up against it in future.

I spent at least a day trying to access the CommandBars collection,
using VSTO without any success. I created a new Shared Add-In project
and tried the same thing with exactly the same results (on a target
machine - the development machine worked perfectly with both VSTO and
Shared AddIn). I tried using the MenuBars collection instead of the
CommandBars collection, but came unstuck when trying to assign an
action to the newly created Menu item.

At this point I decided to give VSTO another try, as I was running out
of time and options. I uninstalled and reinstalled the Office 2003
PIAs on the development machine, as I had an idea that these were the
cause of the problems.

The installation again failed, but this time I suspected Security
permissions, and searched the web for answers. I found that the
SetSecurity project that comes with the VSTO install doesn't support
a) assigning permissions to an entire directory b) assigning
permissions to multiple assemblies. I had to modify the SetSecurity
project to accept a semi-colon-delimited list of assemblies as part of
the parameter list. Then I added the names of all included dlls into
the Custom Actions command-line for the Install action. Finally the
thing started working.

So the list of problems included security/trust permissions, interop
assembly versions, prerequisites and a few other curve-balls related
to Excel & VBA.