This project has moved. For the latest updates, please go here.

Need help due to COMException

Apr 23, 2013 at 8:32 AM
Edited Apr 23, 2013 at 9:12 AM
Following COMException indicates a busy application while iterating through the cells.
I don't really know, why the exception is thrown. In the table i want to iterate through 25x13 cells.
  • InnerException {"Durch den Messagefilter wurde angezeigt, dass die Anwendung ausgelastet ist. (Ausnahme von HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))"} System.Exception {System.Runtime.InteropServices.COMException}
The range will be set by
Excel.Range dataRange = (((Excel.Worksheet)cscExcelApp.ActiveWorkbook.ActiveSheet)).get_Range("A" + rowNumber + ":M" + rowNumber);
I wanna put every row in a object (every cell is like a property in the object) having property getters and setters to pick up later the values for further processing in another worksheet. Every row is logically indepent of the others except the fact that every column has a special property e.g. column1 indicates the name property of the data object

You should know, i am regular a java developer, so i am used to beans to abstract to real world object.
For my special task i use C#.
Have i use System.Data.DataTable to convert the cells or is there a short way (independent of ole db)?
Apr 23, 2013 at 9:31 AM
Hello,

There are several ways to do it.

The one I'm using is to say, that every object in Excel is a shape, which means it can have an AlternativeText
shape.AlternativeText
Now, when I put an object into a row, I give him an alternative text
shape.AlternativeText = ConvertToAlphaColumnReferenceFromInteger(sheet.Application.ActiveCell.Column)+ sheet.Application.ActiveCell.Row
If you want to keep it's reference, while Drag&Drop you can give him as Alternative text the current cell that you select.

For the procedure, you might want to look it up here: http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa

Now in order to simulate that object you can use the class
using System.Object;
using System.Windows.Forms.DragDropEffects;
using BrightIdeaSoftware.SimpleDragSource;
With those, you can work with the object and simulate the Drag and Drop.

Now for your getters and setters.

Since every object that you import, is actually a shape, you can access its properties with
worksheetnumber = 1;
foreach (Excel.Worksheet sheet in workbook.Sheets)
                    {
                        sheet.Activate();
                        workSheet = (Excel.Worksheet)xlsApp.Worksheets[worksheetnumber];
                        Console.WriteLine(workSheet.Name);
                        foreach (Excel.Range cell in workSheet.UsedRange)
                        {
                            int rowIndex = cell.Row;
                            int columnIndex = cell.Column;
                            if (cell.Text.Equals("<some test>"))
                            {    
                                cell.Value = <your new value>
                                //Console.WriteLine("Changement done." + cell.Text);
                            }
                        }
                        worksheetnumber++;
                    }
If you want to test what type the shape is of your object
shape.Type.ToString().Equals("msoTable");
You want probably to change your object/shape?
sheet.Range("A1").PasteSpecial(); //Paste a new object in your current row/column
If you want only the used data in your worksheet, you can use:
sheet.UsedRange
I think, you have now enough answers to start with something.

If you are interested in something more complex, you might have a look at http://www.microsoft.com/en-us/download/details.aspx?id=5124

Open XML SDK, this is the next generation to work with.

This was one of my post and the reply of the forum master: http://social.msdn.microsoft.com/Forums/en-US/officegeneral/thread/90e639e5-1acc-460a-bb02-094f089c3693
Apr 23, 2013 at 9:51 AM
Thx Mike,

i have now another approach. I wanna share it l8ter.
Apr 23, 2013 at 11:29 AM
Edited Apr 23, 2013 at 12:37 PM
Hi all,

i am not an expert of .NETOffice, but i think there's sth wrong with the release of the COM Objects.
As i am using .NETOffice to accomplish my task, i've found a hint which brings me to this conclusion
To avoid these errors, implement an IOleMessageFilter handler function in your application. When you do this, if your external application thread calls into Visual Studio and is rejected (that is, it returns SERVERCALL_RETRYLATER from the IOleMessageFilter.HandleIncomingCall method), then your application can handle it and either retry or cancel the call. To do this, initiate the new thread from your Visual Studio application in a single-threaded apartment (STA) and surround your automation code with the IOleMessageFilter handler.
Well, i've also read that NetOffice will accomplish the release of the COM objects for me. I have only to dispose the app like in the examples. Am i rght? So why I've got still SERVERCALL_RETRYLATER? I don't think that i have to implement an IOleMessageFilter.

Isn't my console application per default a STAThread? Should i have to be aware about the COM?

Please don't misunderstand me, i appreciate the work in NetOffice!
Coordinator
Apr 23, 2013 at 1:28 PM
excel is not acessable trough code in some situations. while calculate macros or the user is editing a cell for example.
you got the RETRYLATER exception in this case.

the latest source state contains a message filter with more support to handle this scenario.
you can do something like this:

NetOffice.Settings.MessageFilter.RetryMode = RetryMessageFilterMode.Delayed;
NetOffice.Settings.MessageFilter.Enabled = true;

Sebastian
Apr 23, 2013 at 2:36 PM
Edited Apr 23, 2013 at 2:37 PM
Thx Sebastian ;)

2 issues blocked my coding.
  1. I holded the information in a collection, so it was my responsibilty to clear the list in the end after processing the data.
  2. Following assignment was called too early
myExcelApp.DisplayAlerts = false;
This assignment should be called as late as possible. The reason was a messagebox which popped up asking read/access mode. So i used it before disposing the app. Afterwards everything was ok. But i wanna use your recommendation if this happens again.
Apr 23, 2013 at 3:13 PM
Hi Sebastian, are you sure about the suggested above assignments. It seems that NetOffice.Settings has no definition for MessageFilter.

I have only found this:
NetOffice.Settings.EnableMessageFilter = true;
Coordinator
Apr 23, 2013 at 3:27 PM
yes, im sure. please read my message again. specialy the part 'latest source state'
Apr 23, 2013 at 3:55 PM
Edited Apr 23, 2013 at 3:59 PM
My latest download is NetOffice 1.5.1.2 [RC]. Isn't it the latest source state?
What's your suggestion? Should i take the latest source and compile it on my own?

Sorry for the dumb question!
Coordinator
Apr 23, 2013 at 5:00 PM
this means the latest release.
please checkout:
https://netoffice.codeplex.com/SourceControl/list/changesets
the latest state is: 98664

Sebastian
Apr 24, 2013 at 8:03 AM
Ok, thx. I am just checking out
Apr 24, 2013 at 9:08 AM
Edited Apr 24, 2013 at 12:02 PM
Still get COMException with HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER)
InnerException with -> HRESULT -2147467259

Stacktrace:
bei System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
bei System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
bei System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
bei NetOffice.Invoker.Method(COMObject comObject, String name, Object[] paramsArray)


Any idea?
Apr 26, 2013 at 11:13 AM
Edited Apr 26, 2013 at 11:14 AM
A heavy burden, but I've got it. As i opened the workbook i havn't set notification to false (only Type.Missing was set). Due to this there was a hidden dialog behind the excel app.
It has broken the further processing of the code and caused a COMException (unhappily i couldn't really locate the problem). Luckily i read in a VBA forum that there is a notify property when open method of workbook is used. So i tried it and succeeded :)