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?
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.