HRESULT 800ac472 from set operations in Excel

Question

Hi everyone!

 

I have an interesting problem that I hope someone can give me an idea on how to handle:

If I try any set operations  to for example an Excel.Range when I have start to mark a range of cells and havent finished by releaseing the mouse button, I get a COMException HRESULT 800AC472. It seems that when the mouse is used to select a range, the workbook is set to some particular un-editable state, allthough I can't find any indication of this in either the application, workbook, worksheet or range instance.

 

What I have done is to put a thread on a form which sleeps and does Control.Invoke on a method UpdateCell that will update a value in A1. Using Invoke rather than calling the method directly would ensure that it is actually the main thread that performs the task.  I can fetch the range A1 fine, and I can read the value, it just won't allow me to set either Value2 or Formula.

 

To give you the background:

What I want to do is to figure out what a full product performing long running tasks outputting data to Excel would look like. I soon realized that if an operation induced by a mouse click takes one minute to finish, Excel would be locked down for one minute, which is not acceptable. Spawning a thread to perform the background work would be ideal, but now it turns out that I can't output data particularly when the user is selecting a range. It seems there are several more operations that I can't perform either, like Worksheet.Activate. Any "set" operation really.

 

I use .NET2.0, Excel 2003 SP2 (11.8134.8132) and IDTExtensibility2.

 

Any ideas on how to solve this problem are GREATLY appreciated!!!

 

Cheers!

 

./Fred

 

Answer 1

Hi Fred,

 

Yes, you are getting the VBA_E_IGNORE error that Excel will return when you try to invoke the object model when the property browser is suspended.  This will happen around user edits to ensure that things don't get out of whack with automation slipping in in the middle.  What Excel is telling you is simply that it isn't ready to handle your call at the time you are making it.  This is the fundamental issue with attempting to automation cross-thread; as a caller you have no way of knowing whether the server is in a state where they will be able to accept your call.  For this reason, you must always be able to deal with rejection. 

 

The problem with VBA_E_IGNORE is that it is non-standard.  The COM prescribed way of addressing this issue is to register an IMessageFilter implementation.  This allows COM to notify the server whenever another thread is trying to make a call and this gives the server the opportunity to reject the call if they aren't in a position to handle it.  The client should also register an IMessageFilter which will allow COM to let them know that their call was rejected and give them an opportunity to wait/retry.  Typically, in this situation, clients will wait for a few seconds and retry and if they are unsuccessful after a few retries they will display the OLEBusy dialog. 

 

However, VBA_E_IGNORE occurs outside of this mechanism so you will have to roll your own mechanism of handling it.  I would suggest that you create a some sort of dispatch loop whereby you handle the specific COM exception by waiting a few seconds and then jumping back to the start of the loop.  If too much time elapses without success, you can P/Invoke OLEUIBusy to display the standard busy dialog and let the user know that very likely they need to complete a UI gesture in Excel. 

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

Answer 2

Hi Geoff,

 

Thank you so much for your very prompt and initiated answer!!

I will implement according to your suggestions.

 

Cheers!

 

./Fred

Answer 3

Geoff,

 

Will you offer me some advice on this topic?

 

I am getting this same COMException (HRESULT: 0x800AC472) in my code.  My application has a feature that processes hundreds of Excel workbooks in batch, one at a time.  The code does hundreds (possibly thousands) of Excel object model calls over the life time of the batch process which could run for hours.  It is initiated from an event and runs until it is finished.  I coded this process in a thread.  I have already implemented IMessageFilter per your suggestions in other posts.  I have received this exception on different object model calls, but most of the time it happens on a Range.Delete call.

 

Can you elaborate on your suggestion to "roll your own mechanism"?  Do you mean that I should retry the object model call that failed?  What if it continuously fails?  Given the nature of my batch process, I am not able to "rollback" the processing that has already been done and start over.

 

Also my process is modal.  That is, I prevent the user from doing anything else in Excel while this is running.  There are no other user initiated events or things going on, as far as I can tell.  Your last paragraph implies that the user is doing something in Excel that interferes with my thread and I should display the standard busy dialog to tell them to complete their action.  However in debugging this, I have watched my process run for about 20 minutes before excepting.  I did not touch the keyboard or mouse at all during that time, except to launch the process in the first place.  Unless I'm missing something, there is no UI gesture to complete.

 

One more thing....  (Maybe this is somehow interfering.)

 

Your post here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1335427&SiteID=1 suggests that it is a bad idea to thread Excel object model calls at all.  The only reason I coded my process in a thread is because I don't want to block the UI thread.  My process is lengthy and needs a progress bar so the user does not think the app is dead.  Quote from this article: http://msdn.microsoft.com/msdnmag/issues/03/02/Multithreading/default.aspx

"The general principle is that the thread that's responsible for responding to the user and keeping the user interface up to date (usually referred to as the UI thread) should never be used to perform any lengthy operation."

I also want a cancel button on my progress bar dialog in case the user needs to stop the process at some point.  If I just ran this in the UI event thread, nothing would repaint.  I wish I didn't have to use another thread given all the Control.Invoke issues.  (Collectively my team and I have spent weeks of time trying to get our threads working right.  This latest exception is just another example of why thread coding is difficult.)  However, I know of no way to show a responsive progress bar and allow the user to cancel without coding a separate thread.

 

It seems I'm stuck between a rock and a hard place, as the saying goes.    I need a repaintable cancelable progress bar (Which works best with a thread) and I need to make hundreds of Excel object model calls (which work best without a thread). 

 

Any suggestions would be appreciated.  Thank you.

 

Brandon

Answer 4

Hi Brandon,

 

Yes, 0x800AC472 is the VBA_E_IGNORE error that is returned whenever an object model call is invoked while the property browser is suspended.  Or to put it another way, when Excel developers want to suspend the object mdoel, they suspend the property browser.  There are quite a few places where they do this (handling certain UI gestures is just one of the reasons) and unfortunately, I'm not familiar enough with the Excel code base to be able to tell you what might be happening that is causing it to occur around your delete call.  The real problem with this error (as I mentioned) is that Excel's IMessageFilter implementation doesn't seem to be aware of the suspended state when it is handling incoming calls.  As a result, cross-apartment calls are allowed to enter & then fail outside of the IMessageFilter mechanism. 

 

The only way around that is going to be to make your object model call from a loop.  You will need to continue to retry the call until it succeeds--so you'll set a success bit right after the call and then have an exception handler that catches (and eats) the 0x800AC472 exception which allows the loopback to continue.  If your process is modal, then presumably Excel's suspension of the object model is transient and will resolve on its own if you give it enough time.  You will have to decide for yourself how long you are willing to wait and what action you will take if you reach your timeout. 

 

It is fine to call into the Excel object model on a background thread--provided that you undertand that you aren't getting true concurrency and you recognize the issues involved with cross-apartment marshalling.  In the thread you refer to, what I was objecting to was having multiple UI threads in the same process.  In general this is a really bad idea--multi-threading is hard enough to get right without introducing UI re-entrancy into the mix.  For simple, discrete UI elements that are modal, the multi-threaded approach sometimes makes sense, but actually allowing a user to interface with multiple threads should be avoided.  In fact, there really isn't a good reason to attempt such an implementation since user input is effectively single threaded (all input is handled by the Raw Input thread which is joined to the UI thread of the focused window).  This is what I was objecting to in the thread that I mentioned. 

 

With any UI programming, the key to responsiveness is to not hog the cpu--which means pumping messages on a regular basis.  Typically, time intensive tasks involve processing in a loop, so to get proper re-painting of a progress bar (or responsive handling of a cancel button) you would just need to pump on every loop iteration.  Of course, pumping messages in a loop is just a different form of re-entrancy and your code still has to be able to handle it.  However since your program controls both the timing on the re-entrancy and (more importantly) which messages actually get processed, it can make things much simpler.  In your progress bar case, you would just need to handle WM_PAINT and WM_LBUTTONUP/DOWN messages (for the cancel button click) and you could ignore most everything else which greatly reduces the reentrancy surface. 

 

I'm honestly not sure whether running on a background thread could be contributing to the VBA_E_IGNORE problem you are encountering, but it would be simple enough to test.  If it does turn out to be the problem, you can rewrite your progress bar as I suggest above. 

 

Hopefully this will give you some ideas.

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

Answer 5

Thank you Geoff,

 

I implemented your suggestion and made my object model call from inside a loop.  I caught and ate the specific VBA_E_IGNORE exception.  I decided to retry the Excel object model call a set number of times with a Thread.Sleep() call before the retry, increasing the wait time every time thru the loop.  I am still getting the exception, but all my tests so far have retried the call which was accepted by the object model the second time thru the loop.

 

The one down side to this is that, in theory, all of my excel object model calls need to be put inside this loopback solution.  Well, maybe not all, but at least all of my object model calls that will be made from my background thread.  For my particular task, that is a fair amount of re-work. 

 

At least it is currently working.    Thank you for your time and ideas.

 

Brandon

Answer 6

Geoff,

I am having a similar problem, but I'm not sure what you have said in this thread applies to my situation or not.  I have an ActiveX object that is embedded in Sheet1 of an Excel workbook.  The ActiveX object has information about data in the same sheet in the xlR1C1 format, and it needs it in the xlA1 format, so it calls ConvertFormula.  The HRESULT returned by ConvertFormula is 800ac472... when it fails.

I realize all this is basically the same so far.  The difference with my situation is that ConvertFormula only fails in a pretty specific situation.  If the workbook is saved with Sheet1 selected, the workbook opens, ConvertFormula succeeds, and the ActiveX object is able to work with the data.  If the workbook is saved with Sheet2 (which is empty) selected, ConvertFormula fails when Sheet1 is selected after the workbook is opened.  If, when the workbook is opened, you quickly select Sheet1, sometimes ConvertFormula doesn't fail.

I have put my call to the code that uses ConvertFormula in a loop, and when it fails I sleep for  a second and then try again.  The call fails every time if it fails the first time. 

I have a feeling that this won't be enough information to get a good answer, but I thought I would start with this.  If you think there might be something I can try, please let me know.

Thank you.

Answer 7

Hi,

I have a similar problem being reported by customer.

I have a datagrid on a windows application and the upon a button click the data will be exported to excel. Here during development and testing things are fine but at customer end they are receiving following error.

system.runtime.interopservices.comexception (0x800AC472): exception from HRESULT: 0x800AC472 at system.runtimetype.forwardcalltoinvokemember(string membername, bindingflags flags, object target, int32[] awrappertypes, messagedata& msgdata) at microsoft.office.interop.excel.range.set_default(object rowindex, object columnindex, object)

 

I am not using any range object. Its just a plane export of data. i am usinh excel object 11.0.

 

following is my code.

 

dtExcel is dtatable having data binded to datagrid.

 

Excel.Workbook xlWorkBook;

xlWorkBook = new Excel.Application().Workbooks.Add(true);

Excel.Worksheet xlWorkSheet;

xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;

try

{

xlWorkBook.Application.Visible = true;

xlWorkSheet.Name = "Prod BOM-"+partNumber;

xlWorkSheet.Columns.AutoFit();

for(int jRow=0;jRow<dtExcel.Rows.Count;jRow++)

{

for(int jCol=1;jCol<dtExcel.Columns.Count;jCol++)

{

//xlWorkSheet.Cells[jRow+2,jCol]=dtExcel.Rows[jRow][jCol].ToString ();

xlWorkSheet.Cells[jRow,jCol]= dataGrid1[jRow,jCol].ToString ();

}

}

 

Any Help would be a great Help.

Answer 8

I also have a smilar problem. What I've found to work is putting some "Sleepers" in your code. Excel is extremely slow; like drinking water. Too fast and you choke. Excel is slow mainly beceause Excel was designed an anpplication. So it is best you pause after sometime. So just use Sleep(50); 

Answer 9

I have the same issue. But I still don't know why this conflict happens between 2 sessions.

I have a VB program that trying to Open/Change certain Excel page from a new session at the background (xApp = New Excel.Application; xApp.Visible =False).

Meanwhile, if the user has another excel session already running before my program start, and he is working in that session on some other XLS file, the exception may occur from my VB program. This problem occurs occasionally, but it's critical to the system.

So my question is this, if the property browser is suspended in a Excel session, will the other sessions be affected as well?

Regards.

Answer 10

For a work-around, you can add a Transparent image (separately stored in your media library) to your Form that can be shown in the Dialog or you can use solution model to dynamically add an image to the form before the form gets loaded. Make sure the transparent property of the Image has been set to true. Now, you can set the height and width of the image to the height and width of the Dialog to create the same illusion for showing a busy indication. But, this will not block the user intervention. User can still interact with the form’s contents. To Block the user Intervention, just attach a blank method to the image on its on-action event- just a blank method! So, when a user clicks on any of the form content, s/he ultimately clicks on the image and method attached with the on-action event will get fired, which will do nothing, but prevent user intervention.

---

Cheers, Eliza

Answer 11

Hi Geoff.

 

Do you know if anything changed from .net 3.5 to .net 4 (full not client) that would impact the way an IMessageFilter is registered against the currently running thread?  Code from Andrew Whitechapel's post (http://blogs.msdn.com/b/andreww/archive/2008/11/19/implementing-imessagefilter-in-an-office-add-in.aspx) works fine in .net 3.5 targeted excel add-ins, but it appears that the CoRegisterMessageFilter call fails to register the MessageFilter in .net 4 targeted add-ins.

As an example, if you run the attached project from Andrew's link as a 3.5 targeted project (the default) it works fine.  If you change it to a .net 4 project it fails to call the ReTryRejectedCall when the Excel UI thread is busy and you're back to getting the HRESULT: 0x800AC472 exception.

 

Thanks.

Adam

Answer 12

(Your Excel object).Application.Interactive = false; //meaning the user is not supposed to to change anything on Excel while your program is doing the work.

In my case, this solved it. The damned exception no longer raised.

Do not forget to set it to true when the work is done.

Answer 13

if (your Excel object).Application.Ready

{ ... }

the answer to everything.  Also wrap in a try/catch. And loop, with short wait until done

Answer 14

我也遇到这样错误提示，在本地电脑上测试完全通过，但上传到服务器上，就时常偶尔的提示这个错误，发现是服务器上的OFFICE程序没有激活的原因。

希望我的回答，对后来人有帮助 ，谢谢

Answer 15

Yes, you are getting the VBA_E_IGNORE error that Excel will return when you try to invoke the object model when the property browser is suspended. This will happen around user edits to ensure that things don't get out of whack with automation slipping in in the middle. What Excel is telling you is simply that it isn't ready to handle your call at the time you are making it. This is the fundamental issue with attempting to automation cross-thread; as a <g class="gr_ gr_68 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="68" id="68">caller</g> you have no way of knowing whether the server is in a state where they will be able to accept your call. For this reason, you must always be able to deal with rejection.

The problem with VBA_E_IGNORE is that it is non-standard. The COM prescribed way of addressing this issue is to register an IMessageFilter implementation. This allows COM to notify the server whenever another thread is trying to make a call and this gives the server the opportunity to reject the call if they aren't in a position to handle it. The client should also register an IMessageFilter which will allow COM to let them know that their call was rejected and give them an opportunity to wait/retry. Typically, in this situation, clients will wait for a few seconds and retry and if they are unsuccessful after a few retries they will display the OLEBusy dialog.

However, VBA_E_IGNORE occurs outside of this mechanism so you will have to roll your own mechanism of handling it. I would suggest that you create <g class="gr_ gr_56 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-del replaceWithoutSep gr-progress" data-gr-id="56" id="56">a some</g> sort of dispatch loop whereby you handle the specific COM exception by waiting a few seconds and then jumping back to the start of the loop. If too much time elapses without success, you can P/Invoke OLEUIBusy to display the standard busy dialog and let the user know that very likely they need to complete a UI gesture in Excel.

Answer 16

Hello Brandon,

We have the same problem and I am interested in your solution.

Can you send me the code that you used to implement the messagefilter because I started with this implementation?

Thank you in advance.

Yours truly.

Damien