.Net Interop Excel ExportAsFixedFormat() Exception HResult: 0x800A03EC

Question

I'm currently implementing a method that generated multiple sheets and export them as PDF. For this I'm using the Microsoft.Office.Interop Library (v14.0.0.0) with .NET 4.5.2 . Running Office is 2016

My code:

Dim excel As New Application()
excel.Visible = False
excel.DisplayAlerts = False
Dim workbooks As Workbooks
workbooks = excel.Workbooks
Dim workbook As Workbook = workbooks.Add(Type.Missing)

[...]

workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, String.Format(<a Path>)
ReleaseComObject(workSheet)
workbook.Close()
ReleaseComObject(workbook)
excel.Quit()
ReleaseComObject(excel)

The ReleaseComObject() looks like this (according to Microsoft Support):

Private Sub ReleaseComObject(objectToRelease As Object)
        While System.Runtime.InteropServices.Marshal.ReleaseComObject(objectToRelease) > 0
        End While
        objectToRelease = Nothing
End Sub

This works fine if I run this code for one iteration BUT I noticed that the EXCEL-Process is still running.

If I try to do this in batch-mode (in the meaning of a for-loop) I get an excetion when entering the 2nd interation:

System.Runtime.InteropServices.COMException (0x800A03EC): Ausnahme von HRESULT: 0x800A03EC bei Microsoft.Office.Interop.Excel.WorkbookClass.ExportAsFixedFormat(XlFixedFormatType Type, Object Filename, Object Quality, Object IncludeDocProperties, Object IgnorePrintAreas, Object From, Object To, Object OpenAfterPublish, Object FixedFormatExtClassPtr) bei Controller.CreateListing(DataTable data, Int32 year, String mandantShortName) in ...

Line that throws exception:

workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, String.Format(<a Path>)

For reseach/testing I debugged before reentering the loop and killed the excel-process but w/o any changes.

Anyone faced this problem as well? Solutions/Suggestions?

Answer 1

In order to address the issue with Excel not closing properly replace:

Private Sub ReleaseComObject(objectToRelease As Object)
    While System.Runtime.InteropServices.Marshal.ReleaseComObject(objectToRelease) > 0
    End While
    objectToRelease = Nothing
End Sub

With this bit of code as illustrated by Siddharth Rout:

Private Sub ReleaseObject(ByVal obj As Object)
    Try
        Dim intRel As Integer = 0
        Do
            intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        Loop While intRel > 0
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

You could use your While statement instead of the Do statement but I feel it reads better this way. The important bit here is GC.Collect().

You also have to make sure you release in the right order and release everything. This is usually in backwards order. So in your case, start off with the workSheet then the workbook then workbooks and then lastly excel:

ReleaseObject(workSheet)
workbook.Close()
ReleaseObject(workbook)
ReleaseObject(workbooks)
excel.Quit()
ReleaseObject(excel)

This is the code I put together to test:

Dim app As New Excel.Application()
app.Visible = False
app.DisplayAlerts = False

Dim wbs As Excel.Workbooks = app.Workbooks
Dim wb As Excel.Workbook = wbs.Add()
Dim ws As Excel.Worksheet = CType(wb.Sheets(1), Excel.Worksheet)

ReleaseObject(ws)
wb.Close()
ReleaseObject(wb)
ReleaseObject(wbs)
app.Quit()
ReleaseObject(app)

The process starts and once ReleaseObject(app) has been called the process then closes.