| Groups | Blog | Home |
dotnet interop : Setting a value in an Excel cell using C#
Hi,
I 've managed to run successfully the example provided by the Microsoft Q302901 - "How To Build an Office COM Add-in by Using Visual C# .NET" article http://support.microsoft.com/default.aspx?scid=kb;en-us;302901 I want to change the button_click event code so that, when the button is pressed, a value is written at the cell A1 of the active sheet. I tryied the following code: private void MyButton_Click(CommandBarButton cmdBarbutton,ref bool cancel) { // Get the active worksheet. object sheet = applicationObject.GetType().InvokeMember("ActiveSheet", BindingFlags.GetProperty, null, applicationObject, null); // Get range A1 object range = sheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, sheet, new object[] {"A1", Missing.Value}); // Set the value "Hello world" to that range. range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range, new object[] {"Hello world"}); } The above code works fine for the first two invocations. But when the third invocation that sets the value of the range is run, the application throws an exception. If I catch the exception inside a try {} catch{} I get the message: "Exception has been thrown by the target of an invocation." in the Exception.Message property and "mscorlib" in the Exception.Source property. Does anyone know why I cannot set a property although I can get the properties right ? Thanks Emmanuel
Hi
It is strange that I can not reproduce the problem on my side. Here is a link about how to set the value of range within C# including early-binding and late-binding. Binding for Office automation servers with Visual C# .NET http://support.microsoft.com/kb/302902/EN-US/ Also why you did not use early-binding as the KB article below? Do you have any concern? Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257757
http://msdn.microsoft.com/office/understanding/excel/codesamples/default.aspx?pull=/library/en-us/odc_vsto2003_ta/html/excelobj.asp Regards, Eric "Emmanuel" <Emmanuel@newsgroup.nospam> дÈëÓʼþ news:%23ciScjv1EHA.2540@TK2MSFTNGP09.phx.gbl... [quoted text, click to view] > Hi, > > I 've managed to run successfully the example provided by the Microsoft > Q302901 - "How To Build an Office COM Add-in by Using Visual C# .NET" > article http://support.microsoft.com/default.aspx?scid=kb;en-us;302901 > > I want to change the button_click event code so that, when the button is > pressed, a value is written at the cell A1 of the active sheet. > > I tryied the following code: > > private void MyButton_Click(CommandBarButton cmdBarbutton,ref bool cancel) > { > // Get the active worksheet. > object sheet = applicationObject.GetType().InvokeMember("ActiveSheet", > BindingFlags.GetProperty, null, applicationObject, null); > // Get range A1 > object range = sheet.GetType().InvokeMember("Range", > BindingFlags.GetProperty, null, sheet, new object[] {"A1", Missing.Value}); > // Set the value "Hello world" to that range. > range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, > range, new object[] {"Hello world"}); > } > > The above code works fine for the first two invocations. But when the third > invocation that sets the value of the range is run, the application throws > an exception. If I catch the exception inside a try {} catch{} I get the > message: > "Exception has been thrown by the target of an invocation." in the > Exception.Message property > and > "mscorlib" in the Exception.Source property. > > Does anyone know why I cannot set a property although I can get the > properties right ? > > Thanks > > Emmanuel > > >
Thanks Eric for the document links.
Emmanuel [quoted text, click to view] "Eric" <wangshaoquan@msn.com> wrote in message news:uUGex0z1EHA.2180@TK2MSFTNGP10.phx.gbl... > http://support.microsoft.com/default.aspx?scid=kb;EN-US;257757 > > http://msdn.microsoft.com/office/understanding/excel/codesamples/default.aspx?pull=/library/en-us/odc_vsto2003_ta/html/excelobj.asp > > > Regards, > Eric > > > "Emmanuel" <Emmanuel@newsgroup.nospam> дÈëÓʼþ > news:%23ciScjv1EHA.2540@TK2MSFTNGP09.phx.gbl... >> Hi, >> >> I 've managed to run successfully the example provided by the Microsoft >> Q302901 - "How To Build an Office COM Add-in by Using Visual C# .NET" >> article http://support.microsoft.com/default.aspx?scid=kb;en-us;302901 >> >> I want to change the button_click event code so that, when the button is >> pressed, a value is written at the cell A1 of the active sheet. >> >> I tryied the following code: >> >> private void MyButton_Click(CommandBarButton cmdBarbutton,ref bool >> cancel) >> { >> // Get the active worksheet. >> object sheet = applicationObject.GetType().InvokeMember("ActiveSheet", >> BindingFlags.GetProperty, null, applicationObject, null); >> // Get range A1 >> object range = sheet.GetType().InvokeMember("Range", >> BindingFlags.GetProperty, null, sheet, new object[] {"A1", > Missing.Value}); >> // Set the value "Hello world" to that range. >> range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, >> range, new object[] {"Hello world"}); >> } >> >> The above code works fine for the first two invocations. But when the > third >> invocation that sets the value of the range is run, the application >> throws >> an exception. If I catch the exception inside a try {} catch{} I get the >> message: >> "Exception has been thrown by the target of an invocation." in the >> Exception.Message property >> and >> "mscorlib" in the Exception.Source property. >> >> Does anyone know why I cannot set a property although I can get the >> properties right ? >> >> Thanks >> >> Emmanuel >> >> >> > >
Hi Peter,
I will continue this thread only. Sorry for the mess. I' am using Microsoft Excel 2003 in Windows XP and Visual Studio 2003. I checked C:\Windows\Assembly and found two assemblies both named "office": The first one is version 7.0.3300.0 and the other one is version 11.0.0.0. Then I found 10 more assemblies called: Microsoft.Office.Interop.Excel, (...Word, ...Access, etc) All this assemblies are in version 11.0.0.0 I also checked the installation of Office 2003 and I see that ".NET Programmability support" is installed for every office application. So is this installation of PIAs correct ? Thanks Emmanuel [quoted text, click to view] ""Peter Huang" [MSFT]" <v-phuang@online.microsoft.com> wrote in message news:FXH8jJ11EHA.3388@cpmsftngxa10.phx.gbl... > Hi > > It is strange that I can not reproduce the problem on my side. > Here is a link about how to set the value of range within C# including > early-binding and late-binding. > Binding for Office automation servers with Visual C# .NET > http://support.microsoft.com/kb/302902/EN-US/ > > Also why you did not use early-binding as the KB article below? > Do you have any concern? > > Best regards, > > Peter Huang > Microsoft Online Partner Support > > Get Secure! - www.microsoft.com/security > This posting is provided "AS IS" with no warranties, and confers no > rights. >
Peter,
I also tyied to use early binding but this also has failed, having the same behaviour: I can get the name of the active worksheet (for example) but I cannot write a value to a cell. Please help Thanks Emmanuel [quoted text, click to view] ""Peter Huang" [MSFT]" <v-phuang@online.microsoft.com> wrote in message news:FXH8jJ11EHA.3388@cpmsftngxa10.phx.gbl... > Hi > > It is strange that I can not reproduce the problem on my side. > Here is a link about how to set the value of range within C# including > early-binding and late-binding. > Binding for Office automation servers with Visual C# .NET > http://support.microsoft.com/kb/302902/EN-US/ > > Also why you did not use early-binding as the KB article below? > Do you have any concern? > > Best regards, > > Peter Huang > Microsoft Online Partner Support > > Get Secure! - www.microsoft.com/security > This posting is provided "AS IS" with no warranties, and confers no > rights. >
Hi
Yes, it seems that the office 2003 PIA has been installed. Have you tried to set the property using early binding? Did that work for you? Also I think you may try to follow the KB article to write a simple test application to see if there is any problem? Please perform the test so that we can isolate the problem, thanks. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights.
Hi
Is there any problem with the excel workbook(e.g. open as read-only ....) Have you tried to open a new excel workbook to have a try? Also I think you may try to do the same job on another job to see if there it works. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Peter,
I performed the test with the KB article 302902 without changing a single line. The only thing that might be different from the article is that when I am adding the reference, from the COM tab I select: "Microsoft Excel 11.0 Object Library" that has TypeLib Version 1.5. When I select this reference, another reference with the name "VBIDE" appears in the list of my project's references. I execute the sample code and when I press the button I get the message: "Error: Old format or invalid type library. Line: Microsoft.Office.Interop.Excel Do you have any idea what is happenning here ? Thank you Emmanuel [quoted text, click to view] ""Peter Huang" [MSFT]" <v-phuang@online.microsoft.com> wrote in message news:GugziKB2EHA.3388@cpmsftngxa10.phx.gbl... > Hi > > Yes, it seems that the office 2003 PIA has been installed. > Have you tried to set the property using early binding? > Did that work for you? > Also I think you may try to follow the KB article to write a simple test > application to see if there is any problem? > > Please perform the test so that we can isolate the problem, thanks. > > > Best regards, > > Peter Huang > Microsoft Online Partner Support > > Get Secure! - www.microsoft.com/security > This posting is provided "AS IS" with no warranties, and confers no > rights. >
Hi Peter,
I found the solution to my problem. There was nothing wrong with my code, not even with the PIA stuff. The only problem was that I my Regional Settings were not "en-US" but something else. This problem was due to a Microsoft BUG (check KB article 320369). As a work around, I borrowed some code from the above article: // Save cultural settings in oldCI var and set the settings to "en-US" System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); ... call methods and properties using interop // Restore the original cultural settings. System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; The funny thing is that this work around works for both the early and late binding. The problem with the late binding was that I did not receive the "Old format or invalid type library" message. Thank you all I consider this thread closed. Emmanuel [quoted text, click to view] "Emmanuel" <Emmanuel@newsgroup.nospam> wrote in message news:%23SHEwmE2EHA.2644@TK2MSFTNGP11.phx.gbl... > Hi Peter, > > I performed the test with the KB article 302902 without changing a single > line. The only thing that might be different from the article is that when > I am > adding the reference, from the COM tab I select: "Microsoft Excel 11.0 > Object Library" that has TypeLib Version 1.5. > When I select this reference, another reference with the name "VBIDE" > appears in the list of my project's references. > > I execute the sample code and when I press the button I get the message: > "Error: Old format or invalid type library. Line: > Microsoft.Office.Interop.Excel > > Do you have any idea what is happenning here ? > > Thank you > Emmanuel > ""Peter Huang" [MSFT]" <v-phuang@online.microsoft.com> wrote in message > news:GugziKB2EHA.3388@cpmsftngxa10.phx.gbl... >> Hi >> >> Yes, it seems that the office 2003 PIA has been installed. >> Have you tried to set the property using early binding? >> Did that work for you? >> Also I think you may try to follow the KB article to write a simple test >> application to see if there is any problem? >> >> Please perform the test so that we can isolate the problem, thanks. >> >> >> Best regards, >> >> Peter Huang >> Microsoft Online Partner Support >> >> Get Secure! - www.microsoft.com/security >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> > > >
Hi
I am glad that you have found the solution. Cheers! Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
| home · blog · groups | Questions? Comments? Contact the dn |