Range.Value2 in C# (Excel 2007, VSTO 2008)?
After doing intense research on the net, I finally found out that I cannot
simply use VSTO 2008 ThisAddIn to create UDFs (for Excel 2007), at least not without writing any further VBA code to call the UDF defined. I thought this was possible, but read that actually to write UDFs without VBA I either have to use COM AddIns or Automation AddIns. I'd be happy to be proved I'm wrong. So I wrote an Automation AddIn for Excel 2007 (using C#). It again took me a long time to understand that unlike with VB.NET in C# I simply cannot use myRange.Value but must use myRange.Value2 instead. Fact is, myRange.Value simply doesn't exist as a public accessible field in Microsoft.Office.Interop.Excel. (Of course all the code examples on the net fail to mention this fact.) How do I then access and modify the value of a cell? The following code example works fine: public String Foo(object Range) { Type type = Range.GetType(); return type.ToString(); } However, the next example does not work: public String Foo2(object Range) { Excel.Range rng = (Excel.Range) Range; Object obj = (Object) rng.Value2; // (Object)rng.Cells[1,1].Value2 does not work either Type type = obj.GetType(); return type.ToString(); } The result in Excel is #VALUE! independent of what type the method parameter actually is. I have no clue whatsoever why I cannot access rng.Value2. Can anyone explain? Thanks. Fabz |
Range.Value2 in C# (Excel 2007, VSTO 2008)?
Alright. Finally, I have found out this is probably a Microsoft bug. As soon
as I access myRange.Value2, a COMException is thrown (which I only saw when debugging): "Old format or invalid type library" error See this article: http://support.microsoft.com/default...b;en-us;320369 And for a bit more background: http://blogs.msdn.com/vsto/archive/2...stin-boyd.aspx As it seems, the bug has NOT been resolved in Visual Studio 2008. Fabz "Fabz" wrote in message ... After doing intense research on the net, I finally found out that I cannot simply use VSTO 2008 ThisAddIn to create UDFs (for Excel 2007), at least not without writing any further VBA code to call the UDF defined. I thought this was possible, but read that actually to write UDFs without VBA I either have to use COM AddIns or Automation AddIns. I'd be happy to be proved I'm wrong. So I wrote an Automation AddIn for Excel 2007 (using C#). It again took me a long time to understand that unlike with VB.NET in C# I simply cannot use myRange.Value but must use myRange.Value2 instead. Fact is, myRange.Value simply doesn't exist as a public accessible field in Microsoft.Office.Interop.Excel. (Of course all the code examples on the net fail to mention this fact.) How do I then access and modify the value of a cell? The following code example works fine: public String Foo(object Range) { Type type = Range.GetType(); return type.ToString(); } However, the next example does not work: public String Foo2(object Range) { Excel.Range rng = (Excel.Range) Range; Object obj = (Object) rng.Value2; // (Object)rng.Cells[1,1].Value2 does not work either Type type = obj.GetType(); return type.ToString(); } The result in Excel is #VALUE! independent of what type the method parameter actually is. I have no clue whatsoever why I cannot access rng.Value2. Can anyone explain? Thanks. Fabz |
Range.Value2 in C# (Excel 2007, VSTO 2008)?
Finally, this solved my problem:
http://www.made4dotnet.com/Default.a...bid=141&aid=15 It's ugly, okay, but blame it on MS. Fabz "Fabz" wrote in message ... Alright. Finally, I have found out this is probably a Microsoft bug. As soon as I access myRange.Value2, a COMException is thrown (which I only saw when debugging): "Old format or invalid type library" error See this article: http://support.microsoft.com/default...b;en-us;320369 And for a bit more background: http://blogs.msdn.com/vsto/archive/2...stin-boyd.aspx As it seems, the bug has NOT been resolved in Visual Studio 2008. Fabz "Fabz" wrote in message ... After doing intense research on the net, I finally found out that I cannot simply use VSTO 2008 ThisAddIn to create UDFs (for Excel 2007), at least not without writing any further VBA code to call the UDF defined. I thought this was possible, but read that actually to write UDFs without VBA I either have to use COM AddIns or Automation AddIns. I'd be happy to be proved I'm wrong. So I wrote an Automation AddIn for Excel 2007 (using C#). It again took me a long time to understand that unlike with VB.NET in C# I simply cannot use myRange.Value but must use myRange.Value2 instead. Fact is, myRange.Value simply doesn't exist as a public accessible field in Microsoft.Office.Interop.Excel. (Of course all the code examples on the net fail to mention this fact.) How do I then access and modify the value of a cell? The following code example works fine: public String Foo(object Range) { Type type = Range.GetType(); return type.ToString(); } However, the next example does not work: public String Foo2(object Range) { Excel.Range rng = (Excel.Range) Range; Object obj = (Object) rng.Value2; // (Object)rng.Cells[1,1].Value2 does not work either Type type = obj.GetType(); return type.ToString(); } The result in Excel is #VALUE! independent of what type the method parameter actually is. I have no clue whatsoever why I cannot access rng.Value2. Can anyone explain? Thanks. Fabz |
All times are GMT +1. The time now is 09:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com