In Excel Interop.Excel why would PivotField.ShowDetail = false throws exception?

0

I have a code which tries to Collapse Entire Field. At the very beginning of the foreach loop (with Worksheet), it crashes stating

Exception thrown: 'System.Runtime.InteropServices.COMException' in CSVDiffUtility.exe

Additional information: The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))

Did some research and solved this by adding a thread sleep.

However, I am still encountering

Exception thrown: 'System.Runtime.InteropServices.COMException' in mscorlib.dll

Additional information: Exception from HRESULT: 0x800A03EC

when the code hits field.ShowDetail = false;

Is there any reason why this may be?

PS.

Also, despite my effor to kill the excel application, all of the exception code does nothing. Meaning it leaves the EXCEL.exe is still remaining on task manager.

Any help would be appreciated.

    static void Main(string[] args)
    {
        Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
        Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

        try
        {
            Microsoft.Office.Interop.Excel.Workbook interopExcelWorkBook = excelApp.Workbooks.Open(FileDirectoryKeeper.DiffXLSXPath);
            Thread.Sleep(500); // yes this is necessary
            try
            {
                foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in interopExcelWorkBook.Worksheets)
                {
                    foreach (Microsoft.Office.Interop.Excel.PivotTable pivotTable in sheet.PivotTables() as Microsoft.Office.Interop.Excel.PivotTables)
                    {
                        foreach (Microsoft.Office.Interop.Excel.PivotField field in pivotTable.PivotFields() as Microsoft.Office.Interop.Excel.PivotFields)
                        {
                            field.ShowDetail = false;
                        }
                    }
                }

                interopExcelWorkBook.Save();
            }
            catch (Exception e)
            {
                Console.WriteLine("---------------------------");
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
                Console.WriteLine("---------------------------");
            }
            finally
            {
                interopExcelWorkBook.Close(false);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(interopExcelWorkBook);
                interopExcelWorkBook = null;
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("---------------------------");
            Console.WriteLine(e.Message);
            Console.WriteLine(e.StackTrace);
            Console.WriteLine("---------------------------");
        }
        finally
        {
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
            excelApp = null;
        }
... continues on ...

UPDATE :

I was tracing the stack and saw System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) is the one that is causing the issue.

c#
excel
interop
asked on Stack Overflow Jan 5, 2017 by ShinT • edited Jun 20, 2020 by Community

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0