Exception from HRESULT: 0x800A03EC excel.range

Question

I am getting this error Exception from HRESULT: 0x800A03EC in excel.range.
Here is my following code

    Private Sub excelInsertData(rowIndex As Integer, ColIndex As Integer, EDT As DataTable)
    Try

        Dim arr As Object(,) = New Object(EDT.Rows.Count - 1, EDT.Columns.Count - 1) {}

        For r As Integer = 0 To EDT.Rows.Count - 1
            Dim dr As DataRow = EDT.Rows(r)

            For c As Integer = 0 To EDT.Columns.Count - 1
                arr(r, c) = dr(c)
            Next
        Next

        Dim c1 As Excel.Range = CType(xlWorkSheet.Cells(rowIndex, ColIndex), Excel.Range)
        Dim c2 As Excel.Range = CType(xlWorkSheet.Cells(rowIndex + EDT.Rows.Count - 1, EDT.Columns.Count + ColIndex - 1), Excel.Range)
        Dim range As Excel.Range = xlWorkSheet.Range(c1, c2)
        range.Value = arr '---------ERROR THROWN HERE

    Catch ex As Exception
        MsgBox(ex.toString)
    Finally
        EDT.Rows.Clear() : EDT.Columns.Clear()
    End Try
End Sub

I am trying to do is transferring data from a Datatable (EDT in this case) to an Excel Template.

Following are the values range, arr and EDT.

range.Value - Length is 5

same length as range

Value in my DataTable(EDT)

Also, I can attach a template of my 3b.xlsm file if required.

Please ask if I am missing any information which needed to be provided.

Answer 1

The first line just gets the DataTable from a Function. You could also just add a parameter to the Sub and pass one in. This code creates a new Excel file. The first loop adds the column names and the second loop adds the data.

Private Sub FillExcelFromDataTable()
    Dim dt = GetWorksheetData()
    Dim oExcel As New Excel.Application
    Dim oBook = oExcel.Workbooks.Add
    Dim oSheet = DirectCast(oBook.Worksheets.Add, Excel.Worksheet)
    Dim ColumnIndex = 1 'in Excel worksheet
    For Each col As DataColumn In dt.Columns 'This loop adds the header row
        oSheet.Cells(1, ColumnIndex) = col.ColumnName
        ColumnIndex += 1
    Next
    ColumnIndex = 1 'The columns and rows in the spreadsheet
    Dim RowIndex = 2 'The columns and rows in the spreadsheet
    For rowI = 0 To dt.Rows.Count - 1
        For Each col As DataColumn In dt.Columns
            oSheet.Cells(RowIndex, ColumnIndex) = dt(rowI)(col)
            ColumnIndex += 1
        Next
        ColumnIndex = 1 'Reset back to the first column
        RowIndex += 1
    Next
    oBook.Save()
    oBook.SaveAs(Filename:="ExcelDat.xlsx") 'Saved to "C:\Users\xxx\Documents\ExcelDat.xlsx"
    oBook.Close()
    oExcel.Quit()
End Sub