Doing the Excel from .NET COM

microsoft.public.dotnet.framework

5 Apr 2006 4:05 PM

Pavils Jurjans

Hello,

I have the following task: I need to create an Excel macro that would pass
the Worksheet object to COM component, and the code of COM component then
has full control what to do with the Excel Worksheet data - in my case, it
would read the data from cells and build XML file to be stored in specified
location. The COM component would be written in .NET, and registered in
system with COM wrapper.

I want to use external COM component, because I'd hate to write all the
XML-building code in VBA, and also because I have some code handy .NET
classes that can be reused.

Here's the code that I'm currently using:

C# code for the class that will be wrapped as COM:

using System;
namespace ExcelXML {
    public class Report {
    public string Generate(object o) {
      return o.GetType()+"";
    }
}
}

Excel macro, that is using the COM:

Sub Macro()
    Dim o
    Set o = CreateObject("ExcelXML.Report")
    MsgBox o.Generate(ActiveWorkbook.ActiveSheet)
End Sub

To make all this work, you need to register the Com Callable Wrapper proxy.
for the .NET dll to make it available for Excel!

When the macro is launched in Excel, the returned type is
"System.__ComObject". I couldn't find any reference on this type of object
in .NET manuals. Everywhere I read about working with com objects, it is
being said that one needs to create a "wrapper for the COM component using
the TLBImp utility". Well, I certainly don't have any Excel Worksheet COM
DLL that could be used to create a wapper, and I doubt it would help,
because the Worksheet object has number of properties that, in turn, are
other type of objects, and, perhaps, to work with them, I'd need to create
wrappers for those, too!

Maybe I am just going the wrong way here. Maybe there is another suggested
method, how to make external .NET code control objects like Excel worksheet?

I'd be very happy for any advice

With kind regards,

Pavils Jurjans