Copy to excel from VB.Net application causes error 0x800A03EC in some environments
Here is the error:
System.Runtime.InteropServices.COMException, Insert, Exception from HRESULT: 0x800A03EC
All machines are Windows XP with the same version of Excel (2003). Some machines get an error, some don't. We have yet to find a sample.
Here is the code:
Public Shared Sub ExportToExcel(ByVal dt As System.Data.DataTable)
Dim app As New Microsoft.Office.Interop.Excel.Application
app.Visible = False
Dim Book = app.Workbooks.Add
Try
Dim Sheet = CType(Book.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
With Sheet
Dim c As Long = Asc("A")
For Each dc As DataColumn In dt.Columns
.Range(Chr(CInt(c)) & "1").Value = dc.ColumnName.ToString
.Range(Chr(CInt(c)) & "1").Font.Bold = True
c += 1
Next
Sheet.Activate()
Sheet.Range("A2:A2").Select()
Sheet.Paste()
.Columns.AutoFit()
CType(app.ActiveWorkbook.Sheets(2), Microsoft.Office.Interop.Excel.Worksheet).Delete()
CType(app.ActiveWorkbook.Sheets(2), Microsoft.Office.Interop.Excel.Worksheet).Delete()
End With
app.Visible = True
app.UserControl = True
Catch ex As Exception
ExceptionManager.HandledException(ex, "Unable to Copy to Excel.", "You are SOL", , False)
Finally
Book = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
End Try
End Sub
a source to share
COM is being counted. The system keeps a counter every time you use things, so you have to clear those links before exiting like so:
Marshal.ReleaseComObject(myVaraible);
You probably don't need WaitForPendingFinalizers, and even GC.Collect is an expensive call that is unnecessary if you drop every com object using the code above to decrease the reference count.
Also, I'm not sure if you are using the Primary Interop Assemblies, but you need to make sure you are using the correct version of Excel for your targeting, or you might get errors like the one you are experiencing.
a source to share