Closing Excel Workbook - System.Runtime.InteropServices.COMException : Exception from HRESULT: 0x800A03EC

Question

I've put in the following method that returns a value from an XLS file cell :

public static string ReadFromExcel(string filePath, int sheetNum, int xCell, int yCell)
    { 
            List<string> rowValue = new List<string> {};
            var ExcelFilePath = @filePath;

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(ExcelFilePath);
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[sheetNum];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            xlApp.ThisWorkbook.Close(); //<-- here it throws the exception in the title
            xlApp.Quit();

            return xlRange.Cells[xCell, yCell].Value2.ToString();
    }

The current code obviously generates an exception because the I close the Workbook and app and then return the value. What's the way to add

            xlApp.ThisWorkbook.Close();
            xlApp.Quit();

and still return the value ?

Thanks.

Answer 1

Use a variable "returnValue", eg:

var returnValue = xlRange.Cells[xCell, yCell].Value2.ToString();
xlApp.ThisWorkbook.Close();
xlApp.Quit();    
return returnValue;

Update

The error code indicates one of these is the cause:

# as an HRESULT: Severity: FAILURE (1), Facility: 0xa, Code 0x3ec
# for hex 0x3ec / decimal 1004 :
  INVALID_RESOURCETYPE_LOOKSALIVE                               clusvmsg.h     
  JET_wrnColumnNull                                             esent98.h      
# /* Column is NULL-valued */
  NMERR_BLOB_ENTRY_DOES_NOT_EXIST                               netmon.h       
  SQL_1004_severity_16                                          sql_err        
# Invalid column prefix '%.*ls': No table name specified
  SCEEVENT_ERROR_POLICY_QUEUE                                   uevents.mc     
# Notification of policy change from LSA/SAM failed to be
# added to policy queue.
# %1
  ERROR_INVALID_FLAGS                                           winerror.h     
# Invalid flags.
  EVENT_MAN_PROFILE_NO_FILE_ACCESS                              wlevents.mc    
# The user %1 does not have access to the mandatory profile
# located at %2.
  EVENT_UAE_VERIFICATION_FAILURE                                wlevents2.mc   
# Verification of an automatically enrolled certificate has
# failed. (%1) %2
  WPA_MUST_ACTIVATE_NOW_EVENT                                   wpaevent.mc    
# This copy of Windows must be activated with Microsoft
# before you can continue. To activate Windows, please
# contact a customer service representative.
# 9 matches found for "0x800A03EC"

Perhaps your xCell, yCell's aren't in the UsedRange. Can you step through the code "Debug it" and post a screenshot that matches the scenario you're encountering? Thanks.

Solution

What you're encountering is explained here: How do I properly clean up Excel interop objects?

To over come it you can use AutoReleaseComObject or the original VSTO-Contrib. Here is some code to show you how to use it:

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace ExcelInterop
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }        

        Microsoft.Office.Interop.Excel.Application excelApp;

        private void button1_Click(object sender, EventArgs e)
        {
            string path = @"C:\temp\Logfile.CSV";
            int sheetNum = 1;
            string returnValue = string.Empty;
            var missing = Type.Missing;
            int xCell = 1, yCell = 1;

            using (AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application> excelRCWWrapper = new AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application>(new Microsoft.Office.Interop.Excel.Application()))
            {
                var excelApp = excelRCWWrapper.ComObject;
                var excelAppWkBooks = excelApp.Workbooks;
                try
                {
                    using (AutoReleaseComObject<Workbook> excelAppWkBk = new AutoReleaseComObject<Workbook>(excelAppWkBooks.Open(path, false, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing)))
                    {
                        var workbookComObject = excelAppWkBk.ComObject;
                        Worksheet sheetSource = workbookComObject.Sheets[sheetNum];

                        using (AutoReleaseComObject< Range> excelAppRange = new AutoReleaseComObject<Range>(sheetSource.UsedRange))
                        {
                            returnValue = excelAppRange.ComObject.Cells[xCell, yCell].Value2.ToString();
                        }
                        ReleaseObject(sheetSource);
                        workbookComObject.Close(false);
                    }
                }
                finally
                {
                    excelAppWkBooks.Close();
                    ReleaseObject(excelAppWkBooks);

                    excelRCWWrapper.ComObject.Application.Quit();
                    excelRCWWrapper.ComObject.Quit();
                    ReleaseObject(excelRCWWrapper.ComObject.Application);
                    ReleaseObject(excelRCWWrapper.ComObject);

                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                }
            }
        }

        private static void ReleaseObject(object obj)
        {
            try
            {
                while (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0) ;
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                Console.WriteLine("Unable to release the Object " + ex.ToString());
            }
        }
    }
}

Here is the AutoReleaseComObject class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;

namespace ExcelInterop
{
    public class AutoReleaseComObject<T> : IDisposable
    {
        private T m_comObject;
        private bool m_armed = true;
        private bool m_disposed = false;

        public AutoReleaseComObject(T comObject)
        {
            Debug.Assert(comObject != null);
            m_comObject = comObject;
        }

#if DEBUG
        ~AutoReleaseComObject()
        {
            // We should have been disposed using Dispose().
            Debug.WriteLine("Finalize being called, should have been disposed");

            if (this.ComObject != null)
            {
                Debug.WriteLine(string.Format("ComObject was not null:{0}, name:{1}.", this.ComObject, this.ComObjectName));
            }

            //Debug.Assert(false);
        }
#endif

        public T ComObject
        {
            get
            {
                Debug.Assert(!m_disposed);
                return m_comObject;
            }
        }

        private string ComObjectName
        {
            get
            {
                if (this.ComObject is Microsoft.Office.Interop.Excel.Workbook)
                {
                    return ((Microsoft.Office.Interop.Excel.Workbook)this.ComObject).Name;
                }

                return null;
            }
        }

        public void Disarm()
        {
            Debug.Assert(!m_disposed);
            m_armed = false;
        }

        #region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
#if DEBUG
            GC.SuppressFinalize(this);
#endif
        }

        #endregion

        protected virtual void Dispose(bool disposing)
        {
            if (!m_disposed)
            {
                if (m_armed)
                {
                    int refcnt = 0;
                    do
                    {
                        refcnt = System.Runtime.InteropServices.Marshal.ReleaseComObject(m_comObject);
                    } while (refcnt > 0);

                    m_comObject = default(T);
                }

                m_disposed = true;
            }
        }
    }
}

Here is a screenshot showing you I got it working!!