none
Excel interop The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)) RRS feed

  • Question

  • Hi All,

    I am working on some excel automation logic. Basically it writes some values into various cells and runs a macro. This is to be repeated for each record in the database. for e.g. if the  database has 100 records the macro has to be run 100 times after pumping values into cells. My problem is the application runs fine upto around 80 records and from there it is giving the following error and finally application is not able to close the workbook. The Excel.exe process is still running.

    If I give 20 records each and run in 5 batches no problems.

    The error is

    System.Runtime.InteropServices.COMException (0x80010105): The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))
       at Microsoft.Office.Interop.Excel.WorkbookClass.Close(Object SaveChanges, Object Filename, Object RouteWorkbook)

    The code looks something like this

    Try
                        
          oExcel = GetExcelObject()
          oWorkBook = GetWorkBook(oExcel, StrExcelFileName)
          oWorkSheets = GetWorkSheets(oWorkBook)

         'Loop through all the records


         'Write values into cells
         'run the macro


         'End loop                  

    Catch ex As Exception
           Throw New Exception(ex.Message & ex.StackTrace)
    Finally
           System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkSheets)
           oWorkSheets = Nothing
                        
           oWorkBook.Close(False)
           System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkBook)
           oWorkBook = Nothing
                  
           oExcel.Quit()
           System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
           System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oExcel)
           oExcel = Nothing                   
    End Try

    Any idea on how to resolve this. Please share your comments on this.

    Thanks & Regards

     

     

    Wednesday, April 5, 2006 5:34 AM

Answers

  • You will notice that the exception is thrown from Workbook.Close.

    Your main code throws some exception. You catch it and throw another exception. Now you call Close and that fails (I do not know why yet). If you want to know why the original data insertion failed, you would be better off eliminating the catch clause altogether and enabling first chance exceptions (Debug/Exceptions/Common Language Runtime). This way you will be able to see why the original exception was thrown.

    iouri

    ---

    This posting is provided as-is and confers no rights

    Friday, April 7, 2006 11:18 PM
  • Hello Shery,

    maybe it has to do with the garbage collector.
    We had a similar problem with Exchange RPC Connections and Outlook Objects.

    We use a workaround, we count the items processed and after e.g. 20 Items you do a GC.Collect().

    So the Garbage Collector releases the references to COM Objects clearly, and you can go on with your work.

    There's also another approach. Since Excel objects still are COM Objects you can call explicitly a Marshal.ReleaseComObject (object) when you are finished with your e.g. workbook, so it's release from memory also.

    Hope this helps,

    Greets, Helmut Obertanner

    [http://www.x4u.de]

    Saturday, April 8, 2006 6:57 AM
    Answerer

All replies

  • You will notice that the exception is thrown from Workbook.Close.

    Your main code throws some exception. You catch it and throw another exception. Now you call Close and that fails (I do not know why yet). If you want to know why the original data insertion failed, you would be better off eliminating the catch clause altogether and enabling first chance exceptions (Debug/Exceptions/Common Language Runtime). This way you will be able to see why the original exception was thrown.

    iouri

    ---

    This posting is provided as-is and confers no rights

    Friday, April 7, 2006 11:18 PM
  • Hello Shery,

    maybe it has to do with the garbage collector.
    We had a similar problem with Exchange RPC Connections and Outlook Objects.

    We use a workaround, we count the items processed and after e.g. 20 Items you do a GC.Collect().

    So the Garbage Collector releases the references to COM Objects clearly, and you can go on with your work.

    There's also another approach. Since Excel objects still are COM Objects you can call explicitly a Marshal.ReleaseComObject (object) when you are finished with your e.g. workbook, so it's release from memory also.

    Hope this helps,

    Greets, Helmut Obertanner

    [http://www.x4u.de]

    Saturday, April 8, 2006 6:57 AM
    Answerer
  • Thanks for your posts. I have realized that exceptions in the macros in spreadsheets are not properly handled. The team is informed to take care of this. I will implement GC.Collect. Hope this solves the problem. 

    Thanks & Regards 

    Monday, April 10, 2006 3:54 AM
  • I have been getting the same exception, but only on some computers and not on others.  They all have the same OS and MS Office version.  The program itself resides on a network drive and all users involved have full access to the folder.

     

    Any help would be appreciated.

     

    RW

    Wednesday, September 12, 2007 9:40 PM
  • The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))

    I had the same Exception and it was seemingly random.

    It is down to the complexity of the spreadsheet and interop.
    Turning the EXCEL Calculation processing to manual whilst performing interop functions cures it.

    excelApp.Calculation = Microsoft.Office.Interop.Excel.

    XlCalculation.xlCalculationManual;

    This means that no formula or cell references are processed.

    You will need to turn it back to Auto afterwards.

    Thursday, August 13, 2009 5:01 PM
  • hi, i'm getting the same error and i have tried a lot of solutions but it still there can you help me with this?

     

    i have the proper using statements and this is the code, also excel has permissio in the server,

    any idea pls?

     

    static void actualizarExcelSqlArticulos()
            {   
                CBOSesion boSesion = new CBOSesion();
                string Path = boSesion.CBOsRutaArticulosImportar;
                Excel.ApplicationClass app = new Excel.ApplicationClass();

    //ERROR HERE
                Excel.Workbook workBook = app.Workbooks.Open(Path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

    //
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;

                int index = 1;
                object rowIndex = 2;
                object colIndex = 1;
                object colIndex3 = 3;
                CBOArticulo boArticulo = new CBOArticulo();
                string sFolio = "";
                double dCosto = 0.0;
                try
                {
                    while (((Excel.Range)workSheet.Cells[rowIndex, colIndex]).Value2 != null)
                    {
                        sFolio = ((Excel.Range)workSheet.Cells[rowIndex, colIndex]).Value2.ToString();
                        dCosto = double.Parse(((Excel.Range)workSheet.Cells[rowIndex, colIndex3]).Value2.ToString());
                        boArticulo.ModificarPrecioPorFolio(int.Parse(sFolio), dCosto);
                       
                        rowIndex = ++index;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    workSheet.Delete();
                    workBook.Close(null, null, null);
                    app.Workbooks.Close();
                    app.Quit();

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                    workSheet = null;
                    workBook = null;
                    GC.Collect();
                }
            }

    Wednesday, April 14, 2010 6:37 PM
  • Do not set the calculation mode immediately on creating the excel object. Give some time for the object to be created. I opened a work book before setting the property and it works fine.

    Dim

     

    objXL As New Excel.Application

     

    Dim objBook As Excel.Workbook = objXL.Workbooks.Open(sReportFileName, False)

     

    Dim objSheet2 As Excel.Worksheet = CType(objBook.Worksheets("Sheet2"), Excel.Worksheet)

     

    '-----------------------------------------------------------------------------

     

    'Disable auto calculations so the procedure runs fast with out recalculations.

     

    Dim lCalcSave As Excel.XlCalculation = objXL.Calculation

     

    If objXL.Calculation <> Excel.XlCalculation.xlCalculationManual Then

    objXL.Calculation = Excel.XlCalculation.xlCalculationManual

     

    End If

     

    '----------------------------------------------------------------------------------------------------

    Friday, July 9, 2010 3:48 PM
  • Hi All,

    After seeing all the above replies I came to the conclusion that although my error message is same but the cause is different.

    After performing all the operations, when I want to save the excel file I am receiving this error message.

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        def_path = System.IO.Path.GetTempPath
        Dim time As DateTime = DateTime.Now
        Dim format As String = "ddMMyyhhmmss"
        str_time = time.ToString(format)
    
    
    
        My.Computer.FileSystem.WriteAllBytes(def_path & str_time & "ReqNoFormatTemplate.xlsx", My.Resources.ReqNoFormatTemplate, False)
        MyFile = def_path & str_time & "ReqNoFormatTemplate.xlsx"
        xlapp.Workbooks.Open(MyFile)
    
        xlapp.Sheets("Sheet2").Activate()
        Dim cc(4) As Integer
    
        cc(0) = 2
        cc(1) = 2
        cc(2) = 2
        cc(3) = 1
    
    
        With xlapp.ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TextBox1.Text, Destination:=xlapp.Range("$A$5"))
          .Name = "reqno"
          .FieldNames = True
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .TextFilePromptOnRefresh = False
          .TextFilePlatform = 437
          .TextFileStartRow = 3
          .TextFileParseType = Excel.XlTextParsingType.xlDelimited
          .TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierNone
          .TextFileConsecutiveDelimiter = False
          .TextFileTabDelimiter = False
          .TextFileSemicolonDelimiter = False
          .TextFileCommaDelimiter = True
          .TextFileSpaceDelimiter = False
          .TextFileColumnDataTypes = cc
          .TextFileTrailingMinusNumbers = True
          .Refresh(BackgroundQuery:=False)
        End With
        xlapp.ActiveWorkbook.Connections(1).Delete()
    
        xlapp.Columns("C:C").EntireColumn.AutoFit()
        xlapp.Columns("C:C").ColumnWidth = 10.57
        x = 84
        mm = xlapp.Cells(5, 1).Text
        ll = 6
        For i = 5 To x
          jj = xlapp.Cells(ll, 1).Text
          chars = Microsoft.VisualBasic.Left(jj, 2)
          If chars = "00" Then
            ll = ll + 1
            GoTo next_itera
          End If
          For j = ll To x
            char_new = Microsoft.VisualBasic.Left(xlapp.Cells(j, 1).Text, 2)
            If chars <> char_new Then
              xlapp.Rows(j & ":" & j).Select()
              xlapp.Selection.Insert(Shift:=Excel.XlDirection.xlDown, CopyOrigin:=Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)
              xlapp.Range("A" & j & ":" & "Q" & j).Select()
              With xlapp.Selection.Interior
                .Pattern = Excel.XlPattern.xlPatternSolid
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .ThemeColor = Excel.XlThemeColor.xlThemeColorDark1
                .TintAndShade = -0.349986266670736
                .PatternTintAndShade = 0
              End With
              ll = j + 1
              Exit For
            End If
    
          Next
    next_itera:
        Next
    
        'With SaveFileDialog1
        '  .Title = "Select a location to save the formatted Excel file"
        '  .FileName = ""
        '  .Filter = "Excel Files|*.xlsx"
        '  .ShowDialog()
        'End With
        mj = System.IO.Path.GetFileName(MyFile)
        ' kl = SaveFileDialog1.FileName
        '  kl = System.IO.Path.GetFileName(kl)
        ' xlapp.Workbooks(mj).SaveAs(kl)
    
        xlapp.Visible = True
        ' xlapp.Workbooks(mj).Save()
         <strong>xlapp.ActiveWorkbook.Save()
        xlapp.ActiveWorkbook.Close()</strong>
        xlapp = Nothing
      End Sub
    

    I am receiving an exception in the Bold lines. How do I catch this since it is the last step?

     

    Thanks


    Kishlaya Ever Learning Developer:)
    Monday, December 27, 2010 3:52 AM
  •  

    Hi All,

    I have met the same problem while running the following code.

    Please help to resolve this problem, thanks a lot

    =================================================================================

          bool GenerateExcelReport(bool IsDirectSave)
            {
                bool Result = false;


                #region Excel Init
                Excel.Application oXL;
                Excel._Workbook oWB;
                Excel._Worksheet oSheet;
                Excel.Range oRange;
                #endregion

                oXL = new Excel.Application();
                oXL.Visible = true;
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(GlobalResource.TemplateFilePath + this.ReportTemplateName));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;

                try
                {
                    #region Set Data
                    string InvestType = (rbBuy.Checked) ? "B" : "S";

                    if (!string.IsNullOrEmpty(BBIRT.SecID)) SetExeclCall(oSheet, BBIRT.SecID, GlobalResource.ConvertSecID(txtSecurity.Text.Trim()));
                    if (!string.IsNullOrEmpty(BBIRT.InvestType)) SetExeclCall(oSheet, BBIRT.InvestType, InvestType.Equals("B") ? "Buy" : "Sell");
                    if (!string.IsNullOrEmpty(BBIRT.InvestDate)) SetExeclCall(oSheet, BBIRT.InvestDate, Convert.ToDateTime(dtpInvestDate.Text).ToLongDateString());
                    if (!string.IsNullOrEmpty(BBIRT.Reporter)) SetExeclCall(oSheet, BBIRT.Reporter, cbxReporter.Text);
                    if (!string.IsNullOrEmpty(BBIRT.CompanyStatus)) SetExeclCall(oSheet, BBIRT.CompanyStatus, txtComStatus.Text);
                    if (!string.IsNullOrEmpty(BBIRT.BusinessFuture)) SetExeclCall(oSheet, BBIRT.BusinessFuture, txtBusFuture.Text);
                    if (!string.IsNullOrEmpty(BBIRT.Suggestion)) SetExeclCall(oSheet, BBIRT.Suggestion, txtSug.Text);

                    if (!string.IsNullOrEmpty(BBIRT.FY1_1)) SetExeclCall(oSheet, BBIRT.FY1_1, "FY1 " + DateTime.Now.AddYears(-4).Year.ToString());
                    if (!string.IsNullOrEmpty(BBIRT.FY1_2)) SetExeclCall(oSheet, BBIRT.FY1_2, "FY1 " + DateTime.Now.AddYears(-3).Year.ToString());
                    if (!string.IsNullOrEmpty(BBIRT.FY1_3)) SetExeclCall(oSheet, BBIRT.FY1_3, "FY1 " + DateTime.Now.AddYears(-2).Year.ToString());
                    if (!string.IsNullOrEmpty(BBIRT.FY1_4)) SetExeclCall(oSheet, BBIRT.FY1_4, "FY1 " + DateTime.Now.AddYears(-1).Year.ToString());
                    if (!string.IsNullOrEmpty(BBIRT.FY1_5)) SetExeclCall(oSheet, BBIRT.FY1_5, "FY1 " + DateTime.Now.Year.ToString());
                #endregion


                    string OutputPath;
                    if (IsDirectSave)
                    {
                        OutputPath = GlobalResource.UserFolder + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //xls
                    }

                    oSheet.Delete();
                    oWB.Close(true, OutputPath, Missing.Value);

                    if (IsDirectSave)
                    {
                        System.Diagnostics.Process.Start("excel.exe", "\"" + OutputPath + "\"");
                    }

                    Result = true;

                    return Result;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    oXL.Workbooks.Close();
                    oXL.Quit();

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL);

                    oSheet = null;
                    oWB = null;
                    GC.Collect();
                }
            }

    Tuesday, August 14, 2012 8:07 AM