S
strider
Following the example of http://support.microsoft.com/kb/302901/EN-US/
and putting a couple of days effort into it, i just give up. I can not
get the vanilla Excel shared add-in to work.
To get it to load i followed the directions of this sample
http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx and
sort of mashed the two examples together.
After adding the (Un)RegisterFunction() bits, the addin would load,
create an interactive toolbar, but it wouldn't run the Tax function
(shared or otherwise)
I'm at my wits end, the problem I am trying to overcome is this. I
have a whole library of financial functions written in c# we need to
expose to accountants via Excel. They should be able to wire up the
the inputs and get a result in real time. (either a single value or an
array).
I've seen this done VERY sucessfuly in C++ by exposing an interface
for COM Interop and then having a base workbook that does a DLL import
and has a DECLARE for each function. I just want to do this smoothly
and integrate with all the code we already have.
THANKYOU in advace for any help!
Heres the mashed up source:
using System;
using Extensibility;
using System.Runtime.InteropServices;
using System.Reflection;
using Microsoft.Office.Core;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace PricingLibraryAddIn
{
//http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx
//http://support.microsoft.com/kb/908002/en-us
//http://groups.google.com/group/
microsoft.public.office.developer.automation/browse_thread/thread/
f5d903069a4d5890/e348a98d45515a62#e348a98d45515a62
#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons
such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you
wish to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
PricingLibraryAddInSetup project,
// right click the project in the Solution Explorer, then choose
install.
#endregion
/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("7C84EB93-E185-4327-B6F4-62763E2C8A8A"),
ProgId("PricingLibraryAddIn.Connect"),
ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Connect : Object, Extensibility.IDTExtensibility2
{
[ComRegisterFunctionAttribute]
public static void RegisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
Microsoft.Win32.RegistryKey key =
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("", @"C:\Windows\System32\mscoree.dll");
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
}
public string ExecuteQuery(System.String strQuery, [Optional]
object optionalServerIp, [Optional] object optionalPort, [Optional]
object optionalPollInterval)
{
//start processing here asynchronously
return "Executing...";
}
/// <summary>
/// Test function
/// </summary>
/// <param name="income"></param>
/// <returns></returns>
[ComVisible(true)]
public static double Tax(double income)
{
if (income > 0 && income <= 7000) { return (.10 *
income); }
if (income > 7000 && income <= 28400) { return 700.00 + (.
15 * (income - 7000)); }
if (income > 28400 && income <= 68800) { return 3910.00 +
(.25 * (income - 28400)); }
if (income > 68800 && income <= 143500) { return 14010.00
+ (.28 * (income - 68800)); }
if (income > 143500 && income <= 311950) { return 34926.00
+ (.33 * (income - 143500)); }
if (income > 311950) { return 90514.50 + (.35 * (income -
311950)); }
return 0;
}
private CommandBarButton MyButton;
/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}
/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref
System.Array custom)
{
applicationObject = application;
addInInstance = addInInst;
if (connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup)
{
OnStartupComplete(ref custom);
}
MessageBox.Show("Connected");
}
/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
if (disconnectMode !=
Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
{
OnBeginShutdown(ref custom);
}
applicationObject = null;
}
/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate(ref System.Array custom)
{
}
/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete(ref System.Array custom)
{
try
{
CommandBars oCommandBars;
CommandBar oStandardBar;
try
{
oCommandBars =
(CommandBars)applicationObject.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, applicationObject, null);
}
catch (Exception)
{
// Outlook has the CommandBars collection on the
Explorer object.
object oActiveExplorer;
oActiveExplorer =
applicationObject.GetType().InvokeMember("ActiveExplorer",
BindingFlags.GetProperty, null, applicationObject, null);
oCommandBars =
(CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, oActiveExplorer, null);
}
// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars["Standard"];
}
catch (Exception)
{
// Access names its main toolbar Database.
oStandardBar = oCommandBars["Database"];
}
// In case the button was not deleted, use the exiting
one.
try
{
MyButton = (CommandBarButton)oStandardBar.Controls["My
Custom Button"];
}
catch (Exception)
{
object omissing = System.Reflection.Missing.Value;
MyButton =
(CommandBarButton)oStandardBar.Controls.Add(1, omissing, omissing,
omissing, omissing);
MyButton.Caption = "My Custom Button";
MyButton.Style = MsoButtonStyle.msoButtonCaption;
}
// The following items are optional, but recommended.
//The Tag property lets you quickly find the control
//and helps MSO keep track of it when more than
//one application window is visible. The property is
required
//by some Office applications and should be provided.
MyButton.Tag = "My Custom Button";
// The OnAction property is optional but recommended.
//It should be set to the ProgID of the add-in, so that if
//the add-in is not loaded when a user presses the button,
//MSO loads the add-in automatically and then raises
//the Click event for the add-in to handle.
MyButton.OnAction = "!<PricingLibraryAddIn.Connect>";
MyButton.Visible = true;
MyButton.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.MyButton_Click);
object oName =
applicationObject.GetType().InvokeMember("Name",
BindingFlags.GetProperty, null, applicationObject, null);
// Display a simple message to show which application you
started in.
System.Windows.Forms.MessageBox.Show("This Addin is loaded
by " + oName.ToString(), "MyCOMAddin");
oStandardBar = null;
oCommandBars = null;
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Implements the OnBeginShutdown method of the
IDTExtensibility2 interface.
/// Receives notification that the host application is being
unloaded.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnBeginShutdown(ref System.Array custom)
{
object omissing = System.Reflection.Missing.Value;
System.Windows.Forms.MessageBox.Show("MyCOMAddin Add-in is
unloading.");
MyButton.Delete(omissing);
MyButton = null;
}
private void MyButton_Click(CommandBarButton cmdBarbutton, ref
bool cancel)
{
System.Windows.Forms.MessageBox.Show("MyButton was
Clicked", "Pricing Library AddIn");
}
private object applicationObject;
private object addInInstance;
}
}
and putting a couple of days effort into it, i just give up. I can not
get the vanilla Excel shared add-in to work.
To get it to load i followed the directions of this sample
http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx and
sort of mashed the two examples together.
After adding the (Un)RegisterFunction() bits, the addin would load,
create an interactive toolbar, but it wouldn't run the Tax function
(shared or otherwise)
I'm at my wits end, the problem I am trying to overcome is this. I
have a whole library of financial functions written in c# we need to
expose to accountants via Excel. They should be able to wire up the
the inputs and get a result in real time. (either a single value or an
array).
I've seen this done VERY sucessfuly in C++ by exposing an interface
for COM Interop and then having a base workbook that does a DLL import
and has a DECLARE for each function. I just want to do this smoothly
and integrate with all the code we already have.
THANKYOU in advace for any help!
Heres the mashed up source:
using System;
using Extensibility;
using System.Runtime.InteropServices;
using System.Reflection;
using Microsoft.Office.Core;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace PricingLibraryAddIn
{
//http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx
//http://support.microsoft.com/kb/908002/en-us
//http://groups.google.com/group/
microsoft.public.office.developer.automation/browse_thread/thread/
f5d903069a4d5890/e348a98d45515a62#e348a98d45515a62
#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons
such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you
wish to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
PricingLibraryAddInSetup project,
// right click the project in the Solution Explorer, then choose
install.
#endregion
/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("7C84EB93-E185-4327-B6F4-62763E2C8A8A"),
ProgId("PricingLibraryAddIn.Connect"),
ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Connect : Object, Extensibility.IDTExtensibility2
{
[ComRegisterFunctionAttribute]
public static void RegisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
Microsoft.Win32.RegistryKey key =
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("", @"C:\Windows\System32\mscoree.dll");
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
}
public string ExecuteQuery(System.String strQuery, [Optional]
object optionalServerIp, [Optional] object optionalPort, [Optional]
object optionalPollInterval)
{
//start processing here asynchronously
return "Executing...";
}
/// <summary>
/// Test function
/// </summary>
/// <param name="income"></param>
/// <returns></returns>
[ComVisible(true)]
public static double Tax(double income)
{
if (income > 0 && income <= 7000) { return (.10 *
income); }
if (income > 7000 && income <= 28400) { return 700.00 + (.
15 * (income - 7000)); }
if (income > 28400 && income <= 68800) { return 3910.00 +
(.25 * (income - 28400)); }
if (income > 68800 && income <= 143500) { return 14010.00
+ (.28 * (income - 68800)); }
if (income > 143500 && income <= 311950) { return 34926.00
+ (.33 * (income - 143500)); }
if (income > 311950) { return 90514.50 + (.35 * (income -
311950)); }
return 0;
}
private CommandBarButton MyButton;
/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}
/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref
System.Array custom)
{
applicationObject = application;
addInInstance = addInInst;
if (connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup)
{
OnStartupComplete(ref custom);
}
MessageBox.Show("Connected");
}
/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
if (disconnectMode !=
Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
{
OnBeginShutdown(ref custom);
}
applicationObject = null;
}
/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate(ref System.Array custom)
{
}
/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete(ref System.Array custom)
{
try
{
CommandBars oCommandBars;
CommandBar oStandardBar;
try
{
oCommandBars =
(CommandBars)applicationObject.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, applicationObject, null);
}
catch (Exception)
{
// Outlook has the CommandBars collection on the
Explorer object.
object oActiveExplorer;
oActiveExplorer =
applicationObject.GetType().InvokeMember("ActiveExplorer",
BindingFlags.GetProperty, null, applicationObject, null);
oCommandBars =
(CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, oActiveExplorer, null);
}
// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars["Standard"];
}
catch (Exception)
{
// Access names its main toolbar Database.
oStandardBar = oCommandBars["Database"];
}
// In case the button was not deleted, use the exiting
one.
try
{
MyButton = (CommandBarButton)oStandardBar.Controls["My
Custom Button"];
}
catch (Exception)
{
object omissing = System.Reflection.Missing.Value;
MyButton =
(CommandBarButton)oStandardBar.Controls.Add(1, omissing, omissing,
omissing, omissing);
MyButton.Caption = "My Custom Button";
MyButton.Style = MsoButtonStyle.msoButtonCaption;
}
// The following items are optional, but recommended.
//The Tag property lets you quickly find the control
//and helps MSO keep track of it when more than
//one application window is visible. The property is
required
//by some Office applications and should be provided.
MyButton.Tag = "My Custom Button";
// The OnAction property is optional but recommended.
//It should be set to the ProgID of the add-in, so that if
//the add-in is not loaded when a user presses the button,
//MSO loads the add-in automatically and then raises
//the Click event for the add-in to handle.
MyButton.OnAction = "!<PricingLibraryAddIn.Connect>";
MyButton.Visible = true;
MyButton.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.MyButton_Click);
object oName =
applicationObject.GetType().InvokeMember("Name",
BindingFlags.GetProperty, null, applicationObject, null);
// Display a simple message to show which application you
started in.
System.Windows.Forms.MessageBox.Show("This Addin is loaded
by " + oName.ToString(), "MyCOMAddin");
oStandardBar = null;
oCommandBars = null;
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Implements the OnBeginShutdown method of the
IDTExtensibility2 interface.
/// Receives notification that the host application is being
unloaded.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnBeginShutdown(ref System.Array custom)
{
object omissing = System.Reflection.Missing.Value;
System.Windows.Forms.MessageBox.Show("MyCOMAddin Add-in is
unloading.");
MyButton.Delete(omissing);
MyButton = null;
}
private void MyButton_Click(CommandBarButton cmdBarbutton, ref
bool cancel)
{
System.Windows.Forms.MessageBox.Show("MyButton was
Clicked", "Pricing Library AddIn");
}
private object applicationObject;
private object addInInstance;
}
}