473,245 Members | 1,725 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

Excel: AddIn, fill active sheet cell ?


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.ActiveS heet;
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;
}
}
May 16 '06 #1
18 8247
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]
- mv*@spam.guard.caspershouse.com

"Frank M. Walter" <f.******@hotmail.com> wrote in message
news:44***********************@news.freenet.de...

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.ActiveS heet;
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;
}
}

May 16 '06 #2
Hello Nicholas,
the function is being called.
There is no more information about exception.

Frank

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> schrieb
im Newsbeitrag news:%2****************@TK2MSFTNGP05.phx.gbl...
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]
- mv*@spam.guard.caspershouse.com

"Frank M. Walter" <f.******@hotmail.com> wrote in message
news:44***********************@news.freenet.de...

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.ActiveS heet;
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;
}
}


May 16 '06 #3
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" wrote:

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.ActiveS heet;
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;
}
}

May 16 '06 #4
Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...
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" wrote:

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.ActiveS heet;
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;
}
}

May 16 '06 #5
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" wrote:
Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...
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" wrote:

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.ActiveS heet;
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;
}
}


May 16 '06 #6
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" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:D5**********************************@microsof t.com...
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" wrote:
Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...
> 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" wrote:
>
>>
>> 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.ActiveS heet;
>> 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;
>> }
>> }
>>
>>
>>


May 16 '06 #7
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" wrote:
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" wrote:
Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...
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" wrote:

>
> 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.ActiveS heet;
> 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;
> }
> }
>
>
>


May 16 '06 #8
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" wrote:
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" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:D5**********************************@microsof t.com...
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" wrote:
Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...
> 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" wrote:
>
>>
>> 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.ActiveS heet;
>> 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;
>> }
>> }
>>
>>
>>


May 16 '06 #9
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...
May 16 '06 #10
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_Clic kEventHandler( 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" wrote:
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...

May 17 '06 #11
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" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...
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_Clic kEventHandler( 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" wrote:
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...

May 17 '06 #12
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

May 17 '06 #13
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
"Galcho[MCSD.NET]" <ga****@gmail.com> schrieb im Newsbeitrag
news:11**********************@i39g2000cwa.googlegr oups.com...
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

May 17 '06 #14

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>

May 17 '06 #15
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" wrote:
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" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...
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_Clic kEventHandler( 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" wrote:
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...


May 18 '06 #16
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" wrote:
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" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...
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_Clic kEventHandler( 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" wrote:
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...


May 18 '06 #17
YES !!!

I was looking in .NET

THANKS!

Frank
"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:2A**********************************@microsof t.com...
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" wrote:
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" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...
> 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_Clic kEventHandler(
> 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" wrote:
>
>> 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...
>>
>>
>>


May 18 '06 #18
Well, our customer do want it...

Frank

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:B0**********************************@microsof t.com...
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" wrote:
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" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...
> 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_Clic kEventHandler(
> 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" wrote:
>
>> 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...
>>
>>
>>


May 18 '06 #19

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet...
3
by: Otie | last post by:
I am trying to copy the cell contents in an MSFLXGRD control (using VB5) into Excel, retaining the foreground colors of the text and numbers. I have tried using the Clipboard.SetText...
6
by: Matthew Wieder | last post by:
I have the following requirements: Build a stand-alone C# application that asks the user to click in a cell in an Excel spreadsheet, and then displays the address of that cell in the C#...
2
by: Jody L. Whitlock | last post by:
I've gotten the COM Addin to work, my problem is this. I need to allow the user to select a range of cells (All in the same column) and then select my addin. My addin needs to pick up this range...
2
by: Mr. California | last post by:
As a novice VBA programmer, I have a problem with a routine written as a click procedure from a form to open an Excel template, insert some information, print, and (ideally) return control back to...
2
by: hapnendad | last post by:
I am using a selection_change event on a worksheet to launch a userform that will populate the cell when a selection is made from a drop down list box on the form. Selection_change shows form if cell...
0
by: nphankey | last post by:
I'm struggling to tame Excel using Python and COM. Most of the following code is working except for the setting of the activesheet. Also, I would like to know how to release a COM object...
7
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.