How to automaticaly activate automation add-in for excel

[Serjo]

Hi,

I've created Add-in automation for Excel thru VSTO 2005, all works fine, but
it's not convenient to activate it manualy. Is there any register key or how
to activate the add-in during excel louding?

Thanks,
Sergey

 

[XL-Dennis]

Sergey,

Could You give some more details about the project?

Automation Add-in implies You work with functions in Excel.

VSTO 2005 implies that You work with a document-centric project. Or do You
refer to VS 2005 (shared add-in) or perhaps VSTO 2005 SE?

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[Serjo]

Hi Dennis,

Thank you for the reply.

1) I've created automation managed Excel addin thru vs2005
AddIn Looks following:
namespace ExcelAddIn
{
[ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Simulator
{
#region Register UnRegister Add-in
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false);
}
private static string GetSubKeyName(Type type)
{
string s = @"CLSID\{" + type.GUID.ToString().ToUpper() +
@"}\Programmable";
return s;
}
#endregion Register UnRegister Add-in
public string SetUserValue(object instrument, object orderId, object
price,
object quantity, object trigger)
{
blah-blah -blah
}
}
}
2) I've created Excel project thru VSTO
3) I need to generate events in SetUserValue function for vsto Project Excel
and generate appropriate answer (i means i've to rewrite excel formula in
cells)
VS2005 correctly register addin, i've activate it thru automation
window(Tools-->Add-in), addin works correctly, but inoticed that addin and
Excel works in different appDomain. Could you please clarify how to relate
Excel with Addin ?I don't want to use remoting, it's not convinient. Please
let me know if you need more info

Great Thanks

 

[XL-Dennis]

Hi Sergey,

Thanks for the info.

... but i noticed that addin and...
Excel works in different appDomain. Could you please clarify how to relate
Excel with Addin ?I don't want to use remoting, it's not convenient.

When developing Add-ins with VS 2005 the COM Add-ins and Excel have an
'In-Of-Process communication'. However MSFT strongly recommend that we 'shim'
the Add-ins as all managed COM Add-ins otherwise would share one DLL as a
bridge between the managed COM Add-ins and Excel (i e I call it 'mscore.dll
hell').

What happen when we 'shim' a managed COM Add-in is that we 'isolate' it by
loading it into its own AppDomain. What we end up is an 'Out-Of-Process
communication'.

If You will only use one managed COM Add-in (created with VS 2005) then I
prefer to not create a shimmed solution. If not then I suggest using shimming.

When we using VSTO to create COM Add-ins then we per se get a scenario where
all Add-ins are loaded in their own AppDomains as VSTO use a speciell
VSTOLoader. This is per design and therefore we get an 'Out-Of-Process
communication'. It is my understanding that we can't 'override' this behavior.

I hope the above make any sense to You.
---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[Serjo]

Thank you very much for the reply. I'am not sure that correctly understand
your answer. I've one solution(project) for VSTO and automation com addin. I
see that Excel initialize the addin by first request (i.e Excel and
automation addin works on different appdomains ) How can i run the automation
addin and Excel vsto in one appdomain? I need this becuase excel
functionality in addin very simple.

Thanks,
Sergey

 

[XL-Dennis]

Hi Sergey,

OK, let see if I can explain it in better terms:

When Excel loads Windows allocate memory to it. If You have an automation
COM Add-in developed with an 'unmanaged' tool like VB 6.0 and C it will be
loaded in the same memory area as the host application and Windows will treat
it as 'one AppDomain'. We can interact with the COM Add-in via VBA and even
with other Add-ins. All in all, this is the COM world.

A VSTO solution is controled by the CLR which loads the VSTO solution into
it's own AppDomain with the help of a VSTOLoader. This is by design and can't
be controled outside the CLR.
As far as I can understand the VSTO AppDomain is part of the memory area
that Excel is allocated but does not share the same 'AppDomain'. The VSTO
Runtime and the PIA make sure that the VSTO solution can communicate with
Excel and vice versa.

When You ask:

How can i run the automation addin and Excel vsto in one appdomain?

The answer is that You can't load them in one AppDomain.

What do You actually want to achieve when You raise this question?

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[Serjo]

Hi Dennis,

Thank you for the reply,

What do You actually want to achieve when You raise this question?

On the my project i have to use UDF (user defined function), therefore i
have to create com addin. Why UDF? Because only thru udf i can catch
calculation events (i need to know cell addres and can use named range)on
other side i have to overwrite some of a cell's formulas, inser rows, and
other. All this actions i have to do only after events which generated by
addin and i don't what use remoting because i need realtime work. Any ideas?
I think com-addin (not automation com-addin) can help me.

Thanks,
Sergey

 

[XL-Dennis]

Hi Sergey,

Yes as we with a COM Add-in can access the calculation event on the
worksheet level. Below is an example how You can set it up in a VB 6.0 COM
Add-in. All code is in the Connection Class:

Option Explicit
'Here I use early binding to Excel.
Private xlApp As Excel.Application
Private xlWBook As Excel.Workbook
'In order to create events on the Worksheet level.
'You can use all the available events if wanted.
Private WithEvents xlWSheet As Excel.Worksheet

Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal
ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As
Object, custom() As Variant)
Set xlApp = Application
Set xlWBook = xlApp.Workbooks.Open("c:\test.xls")
Set xlWSheet = xlWBook.Worksheets(2)
End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As
AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
'Cleaning up.
Set xlWSheet = Nothing
Set xlWBook = Nothing
Set xlApp = Nothing
End Sub

Private Sub AddinInstance_OnAddInsUpdate(custom() As Variant)
'No code here.
End Sub

Private Sub AddinInstance_OnBeginShutdown(custom() As Variant)
'No code here
End Sub

Private Sub AddinInstance_OnStartupComplete(custom() As Variant)
'No code here.
End Sub

Private Sub xlWSheet_Calculate()
'Whenever the sheet is being recalculated this event kicks in.
MsgBox "I watch the calculation of the worksheet: " & xlWSheet.Name
End Sub

If You prefer to use a managed COM Add-ins then it shouldn't be a problem to
port the above example.

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[Serjo]

Hi Dennis,

Thank you very much for the reply, i'll try to use a managed com addin
instread the automation com addin.

Thanks,
Sergey

 

[XL-Dennis]

You're welcome Sergey and thanks for letting me know.

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/