.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
Go Back  Xtreme Visual Basic Talk > > > .NET Add-in does not work with Excel 2002


Reply
 
Thread Tools Display Modes
  #1  
Old 01-22-2009, 09:30 AM
stuntman stuntman is offline
Newcomer
 
Join Date: Jan 2009
Posts: 5
Default .NET Add-in does not work with Excel 2002


Hi all, I hope someone can help.

I have created a C# .Net DLL add-in for Excel. When I try it on my home machine with WinXP and OfficeXP 2003 it works as expected. When I try it on a couple of machines at work with WinXP and OfficeXP2002 it does not work.

I have spent 2 days solid reading up on this subject in general and I still cannot get to the bottom of the problem. As far as I can work out what I am trying to do should work with Excel 2002.

Some details:
- I am using Visual Studio EXPRESS 2008 (C#).
- The assembly is signed with a Strong Name Key File.
- I have installed the OfficeXP (2002) PIAs.
- I have created a class DLL in accordance with many MSDN and other internet posts.
- I have registered the DLL using RegAsm.exe and GACUtil.exe.
- I have added registry entries for Excel for CURRENT_USER so Excel should know about the DLL.

The DLL adds some Automation functions (Tools-Addins-Automation) which can be accessed in cells within Excel (for example you would enter =MultiplyFirstBySecond(2, 3) in a cell and get a result of 6) and also implements the IDTExtensibility2 methods so that when Excel is opened it creates a button on the toolbar which when pressed pops up a message box. It it this functionality (adding buttons with code behind them) that I want to expand on.

I have attached the zips of the batch and registry files I have created to register the DLL in the GAC and in the registry. I have also attached a zip of the complete project.

I have noted what Mike R says here: Automating Office Programs with VB.Net / COM Interop.

So what help am I after? I would like to know if there is anything I should check with my machine's setup or my project or my registration/installation of DLLs, PIAs etc in order to make my DLL work on my Excel 2002 machine.

Thanks in advance for your help.
Attached Files
File Type: zip ExcelAddIn C# Express Project.zip (357.1 KB, 4 views)
File Type: zip ExcelAddIn Batch file to Register DLL.zip (646 Bytes, 4 views)
File Type: zip ExcelAddIn Reg file to Register DLL.zip (475 Bytes, 4 views)
Reply With Quote
  #2  
Old 01-22-2009, 05:34 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Stuntman,

Welcome to the forum. :-)

I don't have much experience with deployment issues I'm afraid, and you have a lot here to took at. But I'm printing it out and will give it all a look.

The first thing I noticed is that you do not have a Setup project for this, which I think could be useful instead of having to put together your deployment package via a patchwork of .reg and .bat files.

One thing that comes to mind here is that if you are targeting the 2002 PIAs, then the target machine (the ones at work that are not working) would have to have them as well. I don't have the 2002 PIAs on the machine I'm using and so your 'Excel' reference shows up as broken -- because I don't have it.

I don't know if the 2002 PIAs are a problem for you or not -- if they already have them, then this can't be the issue. But I thought I'd bring this up because it's the only thing I've really noticed so far.

Otherwise, let me give this a look, and hopefully I'll have some ideas, but I don't know...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 01-23-2009, 03:33 AM
stuntman stuntman is offline
Newcomer
 
Join Date: Jan 2009
Posts: 5
Default

Hi Mike and thanks for taking the time to look at this.

Regarding the PIAs I didn't install these on my machine at home and it all worked fine with Excel 2003. I then copied the project on to my work machine and it didn't work. What I can't remember is whether or not any of the references were broken at this time. Either way I am sure that before I installed the PIAs on the 2002 machine all the reference links were either OK immediately or I had fixed them such that it would build.

I tried installing the PIAs after reading something about them here: URL="http://msdn.microsoft.com/en-us/library/aa140045(office.10).aspx"]http://msdn.microsoft.com/en-us/library/aa140045(office.10).aspx[/URL]. It was probably the following passage that made me think they were required:

"In order to consume the COM objects exposed by Office XP, you need to use the primary interop assemblies (PIAs) so that the C# compiler can find out about the interfaces and co-classes exposed by Office XP."

But perhaps if I had all the reference links working OK I shouldn't have messed about with PIAs? Perhaps I should they are a Red Herring?

Regarding creating a Setup Project I can't do this as I am using Visual Studio Express. I would probably only have to deploy to 6 machines max so a little bit of fuss with batch and registry files wouldn't be too bad.

I am looking forward to hearing from you again when you have had a chance to look over it further.
Reply With Quote
  #4  
Old 01-23-2009, 07:04 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

PIAs: yes, that was basically a red herring, I'm afraid. For what you're trying to accomplish, it is quite possible that referencing the Excel 2002 PIAs can make things more complicated for you. Everything should be fine, so long as all the computers involved also have the Excel 2002 PIAs installed... but using a local IA can be easier.

A local interop assembly or (local IA) is what occurs when you reference Excel without there being any PIAs on the machine that is being used to run Visual Studio. In this case, the local IA will be placed within your project folder, and when you copy it into the destination machine, the local IA that you copied in will be your IA. It is not strong-named, so it is not a "Primary" IA, but it is a fully functional interop assembly, nonetheless.

The main reason that it is preferable to use Microsoft's provided Primary Interop Assemblies (PIAs) is so that various add-ins and programs can intercommunicate. If every add-in uses its own local IA for its reference to Excel then you cannot pass Excel Application objects (or any other Excel objects) between programs or add-ins.

However, I find that this need is extremely rare, in practice. You would only need it if your Excel add-in is communicating with another add-in and you are passing Excel object model references to one another. This is a very sophisticated approach and is not seen much. Even in this case, one could still pass string names, such as the string name for a workbook, worksheet, or the full path address to a range.

In short: don't be too worried about the PIA situation -- if you get it working using a local IA, then you are fine for what you're trying to do.

But if you now have the Excel 2002 PIAs installed on all the machines in question, then you should already be good to go. You might want to manually inspect the GAC on each of the machines that are not working to be 100% certain that the Excel 2002 PIAs are in fact there.

If this is fine, then what I do in situations like this is start with an extremely small test project that has minimal functionality, see if that works, and then keep adding aspects one at a time, until you hit a problem. For example, in this case, I would:

(1) Create a brand new project from scratch.

(2) Do NOT add any references to Excel -- not yet.

(3) Do add a reference to Extensiblity and implement IDTExtensiblity so that your class loads when Excel opens. Since you are not referencing Excel, you can't actually call it, not using early binding anyway. Have all 5 of the implemented IDTExtensibility interface methods be empty, except the startup method which can have a message box that reports "Startup sucessful!" or the like, so that you know that it is loading. You can put another MessageBox within the shutdown method as well.

(4) Test this project on the development machine: does it run?

(5) Register on one of the bad test machines and try running there. Does it work? If it fails here we know that it's not a PIA issue or anything else... although I suspect/hope that this all works fine.

(6) Add a reference to Excel. At startup, cast the 'object Application' reference that comes in via a parameter to an Excel.Application reference and report the .Version property via a message box.

(7) Test this new version on the development machine.

(8) Register and test on one of the bad machines. Does it run? If it fails here, then you definitely have a PIA or similar referencing issue going on. If it passes this test, however, then it is something else...

Basically, you just keep going, piece by piece, until you find the thing that goes wrong. At some point you will hit it...

I know that this is time-consuming, but I think that this is the only rational way to tackle a complex problem like this. This is the way I handle this kind of thing, anyway.

Good luck with it, and let us know how it goes... Let us know if you get stuck anywhere, and hopefully we can help.

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 01-23-2009 at 09:31 AM.
Reply With Quote
  #5  
Old 01-23-2009, 09:07 AM
stuntman stuntman is offline
Newcomer
 
Join Date: Jan 2009
Posts: 5
Default

Mike thanks again.

I have created a simple project from scratch doing the absolute minimum as you have suggested and it doesn't work. I will do the same at home and see what the result is on the 2003 machine and report back.

Have a good weekend.

David
Reply With Quote
  #6  
Old 01-23-2009, 09:27 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey David,

I'm really sorry to hear this

So simply referening Extensibility without referencing Excel is already an issue?

You might want to back it up a step further: just create a simple forms-based Application that just has a button on it that opens a message box that says "button clicked!" and that's it...

If this does not deploy, then I would think that it's the .NET Framework installation on the target machines that might not be right.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 01-23-2009, 09:45 AM
stuntman stuntman is offline
Newcomer
 
Join Date: Jan 2009
Posts: 5
Default

Hi Mike,

Yes a new project that only references Extensibility has an issue. I should say until I have checked this approach on the 2003 machine I can't be 100% sure it should work.

The project I created was very simple. I tried adding the registry settings and registering it in the GAC as before but no luck.

I assume from all that you've said that you would (normally) fully expect IDTExtensibility2 to work with WinXP and Excel2002?

I have some very extensive forms-based apps (that read/write to/from Excel) and they work fine on the "problem" machine.

David
Reply With Quote
  #8  
Old 01-23-2009, 10:01 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

David,

In addition to Mike's excellen input:

# Install the KB 908002 fix
Download and install it from MSFT site:
http://support.microsoft.com/kb/908002

If that does not resolve it then:

# Use .NET Framework 2.0
I noticed that You in the project targeting version 3.5 so change the targeting version to 2.0 to see if that fix the issue.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #9  
Old 02-02-2009, 02:50 AM
stuntman stuntman is offline
Newcomer
 
Join Date: Jan 2009
Posts: 5
Default

Hi Dennis,

Thanks for your comment - the KB908002 fix did indeed make my project work on my work machine even while still using .NET 3.5. So that is a start!

The problem I now have is deploying the project using only Visual Studio Express. I have had to put this project down for a while as I concentrate on other things.

Thanks all

David
Reply With Quote
  #10  
Old 02-02-2009, 04:15 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

David,

Glad we could fix the issue

I have always used Pro version so I have no experience with VS Express.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002 .NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
 
.NET Add-in does not work with Excel 2002
.NET Add-in does not work with Excel 2002
 
-->