vb.net - excel - windows task issue

Question

I built a .net application that saves data to excel. When I run the application manually the program saves stuff from the data table into excel. The problem is that when I schedule a task to run on my Windows 10 PC I get the below error.

:System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC at Microsoft.Office.Interop.Excel.WorkbookClass.SaveAs(Object Filename, Object FileFormat, Object Password, Object WriteResPassword, Object ReadOnlyRecommended, Object CreateBackup, XlSaveAsAccessMode AccessMode, Object ConflictResolution, Object AddToMru, Object TextCodepage, Object TextVisualLayout, Object Local)

 Public Shared Function CreateExcel() As Microsoft.Office.Interop.Excel.ApplicationClass
    For retry As Integer = 1 To 5
        Try
            clsScrape.SendMail(" Return New Microsoft.Office.Interop.Excel.ApplicationClass")
            Return New Microsoft.Office.Interop.Excel.ApplicationClass

            Exit For
        Catch ex As Exception
            If ex.HResult <> &H80080005 Then Throw ex
        End Try
    Next

    Return Nothing
End Function

Public Shared Sub ExportExceltest(ByVal excel As Microsoft.Office.Interop.Excel.ApplicationClass)

    Try

        Dim dt As New DataTable
        dt.Columns.Add("ID")
        dt.Columns.Add("Name")


        Dim R As DataRow = dt.NewRow
        R("Name") = "MY Name"
        dt.Rows.Add(R)

        Dim strFile As String = "C:\Users\CodeMonger\Documents\Development\Test" & DateTime.Now.ToString("yyyy_MM_dd HH_mm_ss") & ".xlsx"
        '  Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows

            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next




        Next

        clsScrape.SendMail("The datatable was built and this is Right before save")

        wSheet.Columns.AutoFit()
        wBook.SaveAs(strFile)
        wBook.Close()

    Catch ex As Exception
        clsScrape.SendMail("Here is the Issue     :" & ex.ToString)
    End Try


End Sub

Answer 1

Move the construction code of your Excel object to a factory subroutine that will perform a retry when the exception is caught, like this

Function CreateExcel() as Microsoft.Office.Interop.Excel.ApplicationClass
    For retry As Integer = 1 To 5
        Try
            Return New Microsoft.Office.Interop.Excel.ApplicationClass
            Exit For
        Catch ex As Exception
            If ex.HResult <> &H80080005 Then Throw ex
        End Try
    Next

    Return Nothing
End Function

This article details the problems which will a CO_E_SERVER_EXEC_FAILURE:

This problem can arise for an out of process COM server when

1. The machine has a high CPU load and the process takes a long time to start and execute the CoRegisterClassObjects() in less than 120 seconds.

2. The COM server doesn’t register for the right class IDs.

3. The COM server is currently stopping and there is a race condition between CoCreateInstance and the COM server stopping part.

4. There is a security problem in the way the COM server is started (this page seems to suggest misspelled passwords or lacking the “Login as Batch Job” privilege for “Run As..” COM servers, but anyway I would suggest re-verifying this information for your specific configuration)