Excel File Corruption

Add-in Express™ Support Service
That's what is more important than anything else

Excel File Corruption
 
Sean Devenish




Posts: 78
Joined: 2015-11-30
Hi,

we have had a spate of recent disturbing cases where end-users of our Excel templates and add-in have gone to open Excel files from time to time and found them corrupted. Excel repair functions work to some degree but generally remove all formatting, named ranges and have a range of other side-effects that render the documents unusable.

Although infrequent given the scale of usage, the severity of the corruption is of grave concern for us.

Unfortunately the infrequency means it is exceptionally hard to diagnose. From what we can tell, Excel corruption is usually foreshadowed by users experiencing 0x800A01A8 errors where our add-in, at various points, accesses a named range, for example the following stack trace:


System.Runtime.InteropServices.COMException (0x800A01A8): Exception from HRESULT: 0x800A01A8
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
   at Microsoft.Office.Interop.Excel.Name.get_Name()
...our calling code


These errors seem to occur at random, but the breaking on the call to Microsoft.Office.Interop.Excel.Name.get_Name() could occur at one of a number of different points in our application - ie our stack trace below the above line may vary.

We do use async methods extensively throughout the add-in, though never with .ConfigureAwait(false).

Do you have any ideas as to why we would be getting these errors on an ad-hoc basis, and if they could be contributing to workbook corruption?
Posted 14 Aug, 2019 18:21:57 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
Joined: 2004-04-05
Hi Sean,

Thank you for the detailed description.

As far as I know, 0x800A01A8 is NAME_NOT_FOUND, it means that Excel cannot find a property or method name.

We do use async methods extensively throughout the add-in, though never with .ConfigureAwait(false).

Do you have any ideas as to why we would be getting these errors on an ad-hoc basis, and if they could be contributing to workbook corruption?


I think this happens because you are accessing the Excel Object Model from different threads. Please have a look at the "Using Threads" chapter (page 335) of the Add-in Express documentation, hope it will be useful.
Posted 15 Aug, 2019 04:52:15 Top
Sean Devenish




Posts: 78
Joined: 2015-11-30
Hi Dmity,

thanks for your response. We understood 0x800A01A8 to be a general 'Object Required' error that could have a range of possible causes. The named ranges being referenced exist in their contexts so that doesn't seem to be the cause of the problem.

We have reviewed our code again in conjunction with the Using Threads chapter you referred us to. While we use async/await extensively, we don't have any areas of the code we can identify where we create new threads that interact with the Excel object model.

If you have any other ideas or areas you could point us to (on what is very limited information I know) we would be most appreciative.
Posted 18 Aug, 2019 18:05:19 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Hello Sean,

1. ---
I assume you don't use the Excel object model in background threads; please confirm. Is it possible that you use ADXRibbon* components in background threads? Or just access AddinModule.CurrentInstance.ExcelApp from a thread?

Also make sure that you don't use System.Windows.Forms.Application.DoEvents() in your code.

2. ---
Is the issue reproducible if you turn off all other COM add-ins and Excel add-ins? Is VBA code involved in the issue?

3. ---
Is the call made from a FOREACH loop? If so, try to use a FOR loop instead. Is the Name object located on a real workbook or on a workbook corresponding to an Excel add-in? Is the user editing a cell in this moment? Is there a dialog box opened? Any other activity that might relate to the issue?

4. ---
If we have some code, we could replicate the circumstances in which you call Name.Name and check if the issue occurs for us. If you want to share such code with us, you can send it to the support email address; please make sure your email contains a link to this topic.

5. ---
What Excel build are you using?


Andrei Smolin
Add-in Express Team Leader
Posted 19 Aug, 2019 06:56:36 Top
Sean Devenish




Posts: 78
Joined: 2015-11-30
Hi Andrei,

thanks for this, because the issue is not reproducible (it happens ad-hoc only) it has been immensely difficult to diagnose. All we have is some logs of this error being thrown which seems to correlate to prior to workbooks corrupting.

To answer your questions in turn:

1) We do not use Task.Run, ConfigureAwait(false) or any other threading except for the following two:

In the OnSendMessage, which from memory is code that was provided to us by you to help with another issue:

            int _iCounter = 0;
            private void AddinModule_OnSendMessage(object sender, AddinExpress.MSO.ADXSendMessageEventArgs e)
            {
                try
                {
                    if (e.Message == _wmOff)
                    {
                        Serilog.Log.Debug("LoadFormulasRoutine: Unloading... attempt {Attempt}", _iCounter + 1);
                        System.Windows.Forms.Application.DoEvents();
                        AddIn addin = ExcelApp.AddIns.get_Item("HowNow Smart Workpapers Formulas"); //it's the title of the XLL add-in 
                        if (addin != null)
                        {
                            addin.Installed = false;
                            Serilog.Log.Debug("LoadFormulasRoutine: Unloaded");
                        }
                        else
                        {
                            _iCounter++;
                            if (_iCounter < 100) // just in case the XLL is loaded after this add-in has been loaded 
                                this.SendMessage(_wmOff, IntPtr.Zero, IntPtr.Zero);
                            else
                                Serilog.Log.Debug("LoadFormulasRoutine: Cannot get the add-in");
                        }
                        System.Windows.Forms.Application.DoEvents();
                        this.SendMessage(_wmOn, IntPtr.Zero, IntPtr.Zero);
                    }
                    else if (e.Message == _wmOn)
                    {
                        Serilog.Log.Information("LoadFormulasRoutine: Loading...");
                        System.Windows.Forms.Application.DoEvents();
                        ExcelApp.AddIns.get_Item("HowNow Smart Workpapers Formulas").Installed = true;
                        Serilog.Log.Information("LoadFormulasRoutine: Loaded Successfully");
                        UserActions.Calculate(CurrentInstance.ExcelApp);
                    }
                }
                catch (Exception ex)
                {
                    if (!ex.Message.Contains("Invalid index"))
                    {
                        Serilog.Log.Error(ex, "Failed to process message send");
                    }
                }
            }


The second time we use threading is to show a Windows Form Window with a spinning icon while performing tasks in Excel. To do this, we create a new thread to run the loader, which is then closed once we have finished manipulating Excel. We do this to ensure the Excel interaction always occurs on the main thread.



        public static void RunLoader(BackgroundMethod method)
        {
            var loaderId = Guid.NewGuid();
            var windowCentre = (AddinModule.CurrentInstance.ExcelApp.Left + AddinModule.CurrentInstance.ExcelApp.Width / 2) / 72 * 96;
            var windowTop = AddinModule.CurrentInstance.ExcelApp.Top/72*96;
            LoaderInstance loaderInstance = null;

            Log.Debug("[LOADER] Starting Loader. {StackTrace}", Environment.StackTrace);

            var loaderThread = new Thread(() =>
            {

                loaderInstance = new LoaderInstance(loaderId, windowCentre, windowTop);
                loaderInstance.Show();

                try
                {
                    Dispatcher.Run();
                }
                catch (Exception)
                {
                    // ignored
                }
            });

            loaderThread.SetApartmentState(ApartmentState.STA);
            loaderThread.IsBackground = true;
            loaderThread.Start();

            try
            {
                method();
            }
            catch (Exception e)
            {
                HandleException(e, loaderInstance);
            }
            finally
            {
                loaderInstance.Close();
            }
        }



2) We are unable to reproduce the issue, we can only find occasional logs from clients who use our add-in showing the error.

3) The error occurs in multiple places when requesting different named ranges. To find a requested named range, we iterate through the named range collection of a workbook as follows:

      private static Name GetName(Names names, string namedRange, string sheetName = null)
        {
            for (var i = 0; i < names.Count; i++)
            {
                var name = names.Item(i + 1);

                try
                {
                    bool found = false;

                    if (sheetName == null)
                    {
                        found = name.Name == namedRange;
                    }
                    else
                    {
                        var matchName = $"'{sheetName}'!{namedRange}";
                        var secondMatchName = $"{sheetName}!{namedRange}";
                        found = name.Name == matchName || name.Name == secondMatchName;
                    }

                    if (found) return name;

                    ComHelper.ReleaseCom(ref name, $"name "{name.Name}"");

                }
                catch (Exception e)
                {
                    ComHelper.ReleaseCom(ref name, $"name "{name.Name}"");
                    throw;
                }
            }

            return null;
        }


The error is thrown at
var name = names.Item(i + 1);


Note we dispose of the 'Names' COM object from the caller of this method. This code should illustrate the name we are looking for will exist as we are only looping through the collect

4) Given we can't replicate it I won't expect you to do so!

5) I will endeavour to get some Excel build numbers from clients
Posted 19 Aug, 2019 21:00:25 Top
Sean Devenish




Posts: 78
Joined: 2015-11-30
We have observed some additional instances of a 0x800AC472 from the same GetName method occurring after running the aforementioned loader, could this be a connection?

I should have mentioned the 'BackgroundMethod' parameter type is a delegate for the actual work being performed using the Excel object model.
Posted 20 Aug, 2019 02:37:04 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Thank you, Sean.

I would strongly suggest that you get rid of DoEvents() calls.

As said at https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.application.doevents?view=netframework-4.8, DoEvents() makes the window retrieve all messages from the window message queue; it returns once the queue is empty. The problem is the messages: they are actually any kind of messages; all of them are handled by the Excel application and its add-ins. If you expect certain events to occur in a certain order, calling DoEvents in one of the events may produce a different order of the events. If you call DoEvents() in an event, it is possible to reenter the same event handler. But the most unpleasant consequence is: you allow Excel to switch to another context while your code doesn't expect a context change!

At https://blog.codinghorror.com/is-doevents-evil/, they say:
Basically, if you find yourself needing to call DoEvents anywhere, think about starting another thread instead, or using asynchronous delegates.


See also:
- https://stackoverflow.com/questions/5181777/use-of-application-doevents
- https://stackoverflow.com/questions/11352301/how-to-use-doevents-without-being-evil
- https://blogs.msdn.microsoft.com/jfoscoding/2005/08/06/keeping-your-ui-responsive-and-the-dangers-of-application-doevents/

The last page suggests solutions to typical problems.


Andrei Smolin
Add-in Express Team Leader
Posted 20 Aug, 2019 04:11:21 Top
Sean Devenish




Posts: 78
Joined: 2015-11-30
Hi Andrei,

we have removed those DoEvents() references. We also refactored the async version of the runloader method which used a .ContinueWith to close the loader (insead just doing a straight await and then close), and the combination of these seems to have reduced the incidences of our named range errors.

Many thanks for pointing us in the right direction.

Kind regards,


Sean
Posted 20 Aug, 2019 20:01:41 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Hello Sean,

Great! May I ask you to write me in a couple of weeks to let me know about this issue?


Andrei Smolin
Add-in Express Team Leader
Posted 21 Aug, 2019 03:54:55 Top