Calling .NET COM web service wrapper from Excel

Question

I'm trying to call a web service from Excel 2003 module. The way i've implemented it is creating a .NET COM library with all the classes/methods i need to be exposed. When i try to call a method that queries a web service from Excel the execution just stops on that line without any error. May be it has to do with references? I'm using Microsoft.Web.Services2.dll. I have tried putting it in C:\WINDOWS\SYSTEM32 - no luck

Thanks!

Answer 1

To solve the problem i used Access instead of Excel. In Access it was showing me errors. It turned out that the location of all the reference assemblies should be the location of caller application (in this case it was C:\Program Files\Microsoft Office\OFFICE11). Sencondly my web services proxies were loading the endpoint urls from .config file, which in that context was C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE.config

Answer 2

I am not sure if I get the entire picture, but hopefully some of this will help. I think you have Excel vba calling into .NET via a COM interface and then into a SOAP web svc.

You should have the correct PIA installed and referenced by your .NET assembly. Your COM interface should look something like this:

[Guid("123Fooetc...")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface IBar
    {
        [DispId(1)]
         void SomeMethod(Excel.Range someRange);
    }

Implement the interface with a class something like this:

[Guid("345Fooetc..")]
    [ClassInterface(ClassInterfaceType.None)]
    [ProgId("MyNameSpace.MyClass")]    
    public class MyClass : IBar
    {
        public void SomeMethod(Excel.Range someRange)
        {...}
    }

The first thing I would do is to replace your web service call with a real simple method in your .NET code, to make sure your interface and interop wrapper are working right.

Once your skeleton is working right you may want to consider calling your service with an HTTP method instead of using SOAP. For example, here is a simple call using HTTP GET:

  string resource = yourUrl;
  using (WebClient web = new WebClient())
  {
    web.Credentials = CredentialCache.DefaultCredentials;
    someXml = web.DownloadString(resource);
  }
  return someXml; // or do something interesting with Excel range