COM interop & networks

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I have written a .NET component that I am wanting to
consume via VBA in Excel. During testing, I used the
regasm tool to create the COM Callable Wrapper (CCW) and
succesfully managed to implement the component in VBA on
my local machine.

Several people in the company need to access the
spreadsheet at various stages of the day, therefore my
plan is to put it (and the dll) on a network share. This
way, if I make changes to the spreadsheet or dll, I only
have to change it in one place rather than having to
update it on all users computers.

With this in mind, I have placed the spreadsheet on a
network share and registered the assembly on the server
in the same way as I mentioned above. When I open the
spreadsheet on the network share it works perfectly.
However, when I de-register the dll from my local
computer and then try to run it again, I get an error
saying "ActiveX component can't create object". This
obviously means that in spite of the spreadsheet sitting
on a network share, it is trying to access the dll on my
local computer, rather than on the server on which it is
sitting. I can only think that this has been done for
security reasons, but it doesn't help me! I need
the "network spreadsheet" to access the "network dll"
when using the spreadsheet from a remote computer.

Does anybody have any suggestions about how I can get
around this problem (if at all) because I would really
prefer not to have to register the dll on all users
machines.

Thanks!
 
Logic embedded in an XLS doesn't instantiate objects based on the location
of the XLS.

It uses COM, which means, it looks up the object in the local machine's
registry. When you do regasm, you put an entry into the registry on your
local machine that says "if you need a COM object by name X, then you can
get it from location Y" where X is the progid and Y is a path, typically on
the local machine, like c:\yourpath\whatever.dll . You specify these things
when you run regasm.

De-registering the DLL from your local machine - removing the registration
info for the component from the local registry - means it is no longer
accessible. This is basic COM stuff.

What can you do? I see a couple of approaches:

1. host the .NET component as a web service. this means:
a. the .NET logic runs REMOTELY everybody's machine.
b. you need an "app server" machine to host it. Something like your
fileserver, could even be your fileserver, except it's serving up apps in
addition to files. This machine runs IIS and ASP.NET
c. The VBA logic needs to access the .NET component differently; it
should use the Office Web Services Toolkit to invoke the remote web service.
d. you don't need to "register" the .NET web service on each machine.
Instead you publish it at a well-known URL (eg http://myserver/WebSvc1.aspx)
and you embed this URL in the VBA code.
e. because the VBA app connects to a web service, the logic will not work
unless the client machine is connected to the network, and the webservice
machine is up and running. You couldn't copy the spreadsheet to a laptop
and then use it "offline", on an airplane, or whatever. The vbA app would
need to be able to connect to the webservice.


2. register the COM component, using a network share as the location. this
means:
a. still ned to run regasm on each machine.
b. the DLL should be stored on the file share; regasm from there.
c. there are security issues you need to deal with when running .NET
assemblies from file shares. You would need to configure the .NET runtime
on each client machine to allow running of your .NET assembly.
d. when you update the DLL, you can just drop it on the share. Keep the
interface constant, and use the same assembly versions, and the spreadsheet
will pick up the changes. no need to re-run regasm.
 
Back
Top