Copy to excel from VB.Net app causes a 0x800A03EC error in some environments.

Question

Here is the error:

System.Runtime.InteropServices.COMException, Paste, Exception from HRESULT: 0x800A03EC

All machines are Windows XP with the same version of Excel (2003). Some machines get the error, some don't. We have yet to find a pattern.

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

Answer 1

COM is reference counted. The system keeps a counter of each time your using things, so you should clean up those references before you exit like so:

Marshal.ReleaseComObject(myVaraible);

You probably don't need the WaitForPendingFinalizers, and even the GC.Collect is an expensive call that shouldn't be needed if you release each com object using the code above to lower the reference count.

Also I'm not sure if your using the Primary Interop Assemblies but you need to ensure your using the correct version of them for the version of Excel your targeting or you can get errors such as the one your experiencing.