Excel: AddIn, fill active sheet cell ?

  • Thread starter Thread starter Frank M. Walter
  • Start date Start date
F

Frank M. Walter

Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem System.
Nachinstallieren???


namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveSheet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}


return s;
}
}
 
Frank,

Is there any more information to the exception? On top of that, are you
sure that region is populated, and not null?
 
Hello Nicholas,
the function is being called.
There is no more information about exception.

Frank

Nicholas Paldino said:
Frank,

Is there any more information to the exception? On top of that, are
you sure that region is populated, and not null?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Frank M. Walter said:
Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System. Nachinstallieren???


namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveSheet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}


return s;
}
}
 
applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10", Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );
 
Does not work.
The same exception.



Alan said:
applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

Frank M. Walter said:
Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System.
Nachinstallieren???


namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveSheet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}


return s;
}
}
 
Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio 2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class library
and shared add-in projects...

Frank M. Walter said:
Does not work.
The same exception.



Alan said:
applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

Frank M. Walter said:
Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System.
Nachinstallieren???


namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveSheet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}


return s;
}
}
 
Hi Allan,

I can not manage it....
would you like to be so kind to send me your sample project ?
As a zip-file ?
Frank

Please take this address
ivobraun@ gmx.net

THANKS!!!


Alan said:
Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio
2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class
library
and shared add-in projects...

Frank M. Walter said:
Does not work.
The same exception.



Alan said:
applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

:


Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System.
Nachinstallieren???


namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveSheet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}


return s;
}
}
 
Try this code...


namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;


#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
ExcelPlayAreaSetup 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( "01926917-EBC7-4279-A4A1-BFC25871F0CE" ), ProgId(
"ExcelPlayArea.Connect" )]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class fncAdd : Object, Extensibility.IDTExtensibility2
{

/// <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;

}

/// <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 )
{
}

/// <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 )
{
}

/// <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 )
{
}

private object applicationObject;
private object addInInstance;

public string __T()
{
string s = "Test";
// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember(
"Range",BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value });

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty,
null, range, new object[] { s } );


return s;
}




#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}

Alan said:
Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio 2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class library
and shared add-in projects...

Frank M. Walter said:
Does not work.
The same exception.



Alan said:
applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

:


Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System.
Nachinstallieren???


namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveSheet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}


return s;
}
}
 
Try this code...

namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;


#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
ExcelPlayAreaSetup 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( "01926917-EBC7-4279-A4A1-BFC25871F0CE" ), ProgId(
"ExcelPlayArea.Connect" )]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class fncAdd : Object, Extensibility.IDTExtensibility2
{

/// <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;

}

/// <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 )
{
}

/// <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 )
{
}

/// <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 )
{
}

private object applicationObject;
private object addInInstance;

public string __T()
{
string s = "Test";
// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember(
"Range",BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value });

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty,
null, range, new object[] { s } );


return s;
}




#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}

Frank M. Walter said:
Hi Allan,

I can not manage it....
would you like to be so kind to send me your sample project ?
As a zip-file ?
Frank

Please take this address
ivobraun@ gmx.net

THANKS!!!


Alan said:
Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio
2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class
library
and shared add-in projects...

Frank M. Walter said:
Does not work.
The same exception.



applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

:


Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System.
Nachinstallieren???


namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveSheet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}


return s;
}
}
 
Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...
 
Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...


namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#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
ExcelAddInFuncSetup 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("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}


public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing, omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}


Frank M. Walter said:
Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...
 
Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank



Alan said:
Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...


namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#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
ExcelAddInFuncSetup 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("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}


public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}


Frank M. Walter said:
Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...
 
you should install it with MS Office - check detailed options for each
application and select ".NET Programatically support"

VSTO installs primary interops too

I hope this helps
Galin Iliev[MCSD.NET]
www.galcho.com
 
It is installed with original office 2003 CD
Also .NET p.support.

The interops can be see in GAC.

They can not be put as reference to vs2005 project. Why?

Frank
 
The only way to put a reference is editing of vcproj

<ItemGroup>
<COMReference Include="Excel">
<Guid>{00020813-0000-0000-C000-000000000046}</Guid>
<VersionMajor>1</VersionMajor>
<VersionMinor>5</VersionMinor>
<Lcid>0</Lcid>
<WrapperTool>primary</WrapperTool>
<Isolated>False</Isolated>
</COMReference>
<COMReference Include="Microsoft.Office.Core">
<Guid>{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}</Guid>
<VersionMajor>2</VersionMajor>
<VersionMinor>3</VersionMinor>
<Lcid>0</Lcid>
<WrapperTool>primary</WrapperTool>
<Isolated>False</Isolated>
</COMReference>
<COMReference Include="VBIDE">
<Guid>{0002E157-0000-0000-C000-000000000046}</Guid>
<VersionMajor>5</VersionMajor>
<VersionMinor>3</VersionMinor>
<Lcid>0</Lcid>
<WrapperTool>primary</WrapperTool>
<Isolated>False</Isolated>
</COMReference>
</ItemGroup>
 
To reference of Microsoft.Office.Interop.Excel

right click on references --> add reference --> COM Tab --> choose Microsoft
Excel 11.0 Object Library --> OK



Frank M. Walter said:
Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank



Alan said:
Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...


namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#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
ExcelAddInFuncSetup 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("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}


public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}


Frank M. Walter said:
Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...
 
I dont think it's a bug... It's probably a security issues, do you really
want a UDF to modify another cell automatically ? it could overwrite data.

So I dont think your going to be able to modify a cell other than the one
calling the UDF... The way I got around this was to create a tool bar
button...


You could add an

Frank M. Walter said:
Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank



Alan said:
Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...


namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#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
ExcelAddInFuncSetup 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("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}


public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}


Frank M. Walter said:
Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...
 
YES !!!

I was looking in .NET

THANKS!

Frank


Alan said:
To reference of Microsoft.Office.Interop.Excel

right click on references --> add reference --> COM Tab --> choose
Microsoft
Excel 11.0 Object Library --> OK



Frank M. Walter said:
Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank



Alan said:
Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that
buttons
click event ...


namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#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
ExcelAddInFuncSetup 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("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application,
Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}


public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(
this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}


:

Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...
 
Well, our customer do want it...

Frank

Alan said:
I dont think it's a bug... It's probably a security issues, do you really
want a UDF to modify another cell automatically ? it could overwrite
data.

So I dont think your going to be able to modify a cell other than the one
calling the UDF... The way I got around this was to create a tool bar
button...


You could add an

Frank M. Walter said:
Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank



Alan said:
Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that
buttons
click event ...


namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#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
ExcelAddInFuncSetup 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("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application,
Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}


public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(
this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion


}
}


:

Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top