Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
When I get a SheetChange event that contains a cell using my custom function
I cannot modify the value of any cells in the target range. range is the target range passed into the sheet change event gid_col is the column I wish to validate for(int a = 1; a <= range.Areas.Count; a++) { Excel.Range area = range.Areas[a]; for(int r = 0; r < area.Rows.Count; r++) { //Excel.Range cell = (Excel.Range)area.Cells[r,1]; int row = area.Row + r; if((row_changes[row].Equals(3)) &&(area.Column <= gid_col) &&(area.Column+area.Columns.Countgid_col)) { Excel.Range cell = (Excel.Range)area.Cells[r+1,gid_col+1-area.Column]; String f = (String)cell.Formula; try { cell.set_Value(Excel.XlRangeValueDataType.xlRangeV alueDefault, "wtf"); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show("Sheet change error\n" + ex.ToString(),"Localization Addin"); } } } area = null; } the code works fine if no cell in the target range uses my custom function, but blows up with this error otherwise... System.Runtime.InteropServices.COMException(0x800A 03EC): Exception from HRESULT 0x800A03EC. at System.RuntimeTYpe.ForwardCallToInvokeMember(Strin g memberName, ...) at Microsoft.Office.Interop.Excel.Range.set_Value(Obj ect RangeValueDataType, Object) also if I try to change other cell properties, like Font.Italic it has no effect if my custom function is used by any cell in the target This is the code for my custom function using System; using System.Windows; using System.Windows.Forms; using System.Runtime.InteropServices; using Microsoft.Win32; namespace LocalizationFunctions { using Microsoft.Office.Core; using Excel = Microsoft.Office.Interop.Excel; /// <summary /// Summary description for Class1. /// </summary //[GuidAttribute("EE98CECC-2DEF-4d0b-B707-1985711163F5"), ProgId("LocalizationFunctions.Functions")] [ClassInterface(ClassInterfaceType.AutoDual)] public class Functions { public Functions() { // // TODO: Add constructor logic here // } public String TAG(Excel.Range range) { String result = ""; try { for(int a = 1; a <= range.Areas.Count; a++) { Excel.Range area = range.Areas[a]; for(int r = 1; r <= area.Rows.Count; r++) { for(int c = 1; c <= area.Columns.Count; c++) { Excel.Range _cell = (Excel.Range)area.Cells[r,c]; if(_cell.Value2 != null) { String val = _cell.Value2.ToString(); if(val.Length0) { if(result.Length0) { result += "_"; } result += val; } } //_cell = null; } } //area = null; } result = result.Replace(' ','_'); result = result.Replace('\n','_'); result = result.Replace('\t','_'); //result = (String)range.Value2; } catch (Exception ex) { System.Windows.Forms.MessageBox.Show("Bugger2!\n" +ex.ToString(),"Localization Function"); result = "ERROR!"; } return result; } [ComRegisterFunctionAttribute] public static void RegisterFunction(Type type) { Registry.ClassesRoot.CreateSubKey(GetSubKeyName(ty pe)); Registry.ClassesRoot.CreateSubKey("CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\Programmable"); RegistryKey key = Microsoft.Win32.Registry.ClassesRoot.CreateSubKey( "CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\InprocServer32"); key.SetValue("", System.Environment.GetFolderPath(Environment.Speci alFolder.System) "); } [ComUnregisterFunctionAttribute] public static void UnregisterFunction(Type type) { Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(ty pe),false); } private static string GetSubKeyName(Type type) { string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable"; return s; } } } Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi,
Currently I am researching the issue and we will reply here with more information as soon as possible. If you have any more concerns on it, please feel free to post here. Thanks for your understanding! 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi
Based on my research, there seems to be infinite recursion - if in the change event you modify the sheet, which fires another change event, and so on and so forth. So we need to prevent reentrancy in your event handler with a bool flag. bool bChanged = false; void exApp_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target) { if( bChanged ) return; try { bChanged = true; Target.Value2 = "Test2"; Target.set_Value(Excel.XlRangeValueDataType.xlRang eValueDefault, "Test"); Target.Font.Italic = true; } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); } finally { bChanged = false; } } You may have a try. 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
No luck with that
I already had m_excel_app.EnableEvents=false; m_excel_app.ScreenUpdating = false; .... m_excel_app.EnableEvents=true; m_excel_app.ScreenUpdating = true; in my SheetChange function, but I tried your suggestion anyway. It didn't have any effect. ""Peter Huang" [MSFT]" wrote: Hi Based on my research, there seems to be infinite recursion - if in the change event you modify the sheet, which fires another change event, and so on and so forth. So we need to prevent reentrancy in your event handler with a bool flag. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Just tried a few tests and it appears that if I don't use the range passed
into my custom function everything is fine. So it can be reduced to this test case if that helps... public String TAG(Excel.Range range) { String result = ""; try { result = "banana apple"; //comment the following line out to make it work result += range.Value2.ToString(); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show("Bugger2!\n" +ex.ToString(),"Localization Function"); result = "ERROR!"; } return result; } |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi
Do you mean call the TAG function in the SheetChange event? Based on my test, I can not reproduce the problem. Here is my test code. public String TAG(Excel.Range range) { String result = ""; try { result = "banana apple"; //comment the following line out to make it work result += range.Value2.ToString(); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show("Bugger2!\n" + ex.ToString(), "Localization Function"); result = "ERROR!"; } return result; } bool bChanged = false; void exApp_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target) { if( bChanged ) return; try { bChanged = true; TAG(Target); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); } finally { bChanged = false; } } You may have a try. If you still have any concern, can you build a simple reproduce sample and send the whole solution to me via removing "online" from my email address. So that I can reproduce the problem at my side. Thanks for your efforts. 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
No TAG is a custom function being used in one of the cells, if I change the
custom function (which must be getting called automatically to evaluate the cell) as I described I can make the problem appear or disappear. The code for the custom function was in the first email, along with the problematic part of the SheetChange callback function. I'll try and cut down my app to something that I can send you. ""Peter Huang" [MSFT]" wrote: Hi Do you mean call the TAG function in the SheetChange event? Based on my test, I can not reproduce the problem. Here is my test code. public String TAG(Excel.Range range) { String result = ""; try { result = "banana apple"; //comment the following line out to make it work result += range.Value2.ToString(); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show("Bugger2!\n" + ex.ToString(), "Localization Function"); result = "ERROR!"; } return result; } bool bChanged = false; void exApp_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target) { if( bChanged ) return; try { bChanged = true; TAG(Target); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); } finally { bChanged = false; } } You may have a try. If you still have any concern, can you build a simple reproduce sample and send the whole solution to me via removing "online" from my email address. So that I can reproduce the problem at my side. Thanks for your efforts. 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi Gavin,
I have replied to you in the Email. It is strange that I can not reproduce the problem with your code. 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi Gavin,
I can reproduce the problem with your code and now I am trying to contact the related supporting team to do further troubleshooting. Also I want to check if you are still monitor the thread. If yes, please simply drop a note. 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi Gavin,
Currently I am contacting the related supported team. And I will reply to you ASAP! Best regards, Peter Huang Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi Gavin,
Sorry for delay respond. So far we are still troubleshooting the issue. We will get back here and update you ASAP. Best regards, Peter Huang Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi Gavin,
I have replied to you via Email, please have a check. If you still have any concern, please post here. Best regards, Peter Huang Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange event crashes using Range.set_value (C#)
Hi i have a serious problem while i was working in Excel.I want to fetch columns from an excel worksheet and i need to compare it with an sql querry fields,so i tried to open an excle worksheet first.. Unfortunately my code was throwing error like CLSID\{00020819-0000-0000-C000-000000000046} IS NOT VALID OR NOT REGISTERED. But the same exe is working fine in other Pc's except mine.Then i overcome the error with the code Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCultu re System.Threading.Thread.CurrentThread.CurrentCultu re = New System.Globalization.CultureInfo("en-US") after that excel sheet was opening but i cant read the rows or columns in it error throws like "Old format or Invalid Type Library" in the particular statement wherever i used worksheet.methodname Here i put the entire code please help me Imports System.Windows.Forms Imports Microsoft.Office.Core Imports Excel Public Class Form1 Inherits System.Windows.Forms.Form Public xlApp As New Excel.Application Public Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Dim strFileName As String Dim intRows, intCols As Integer Dim r, c As Integer Dim Strquerry As String Dim strpos As Integer Dim strposc As String Dim StrMid As String 'Create a dialog box to find the excel file Dim dlg As New OpenFileDialog dlg.Filter = "Excel Files (*.xls)|*.xls" dlg.FilterIndex = 0 dlg.ShowDialog() strFileName = dlg.FileName Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCultu re System.Threading.Thread.CurrentThread.CurrentCultu re = New System.Globalization.CultureInfo("en-US") Dim xlWB As Excel.Workbook xlWB = xlApp.Workbooks.Open(strFileName) TextBox1.Text = strFileName xlWB.Application.Visible = True System.Threading.Thread.CurrentThread.CurrentCultu re = oldCI Dim xls As Excel.Sheets Dim xlsheet As New Excel.Worksheet xls = xlWB.Worksheets xlsheet = CType(xls.Item(1), Excel.Worksheet) xlsheet.activate() intRows = xlsheet.UsedRange.Rows.Count If intRows < 0 Then intCols = xlsheet.UsedRange.Columns.Count If intCols < 0 Then ' Scroll through all the rows and columns retrieving values. For r = 1 To intRows For c = 1 To intCols Strquerry = TextBox2.Text strpos = InStr(1, Strquerry, "(") strposc = InStr(strpos, Strquerry, ",") StrMid = Mid(Strquerry, strpos, CType(xlsheet.Cells(r, c), Excel.Range).Text) StrMid = Mid(Strquerry, strposc, CType(xlsheet.Cells(r, c), Excel.Range).Text) MsgBox(StrMid) Next Next xlWB.Close(False) xlApp.Quit() xls = Nothing xlWB = Nothing xlApp = Nothing End If End If Catch ex As Exception MsgBox(ex.Message) End Try End Sub End Class thanks in advance Dimple *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SheetChange event restore old value | Excel Programming | |||
How to know what caused SheetChange event.. | Excel Programming | |||
how to handle 'sheetchange' event on 'add-in' | Excel Programming | |||
SheetChange Event | Excel Programming | |||
SheetChange Event | Excel Programming |