Unable to cast object of type 'System.Data.DataTable' to type 'Microsoft.Office.Interop.Excel.DataTable'.

See more:

VB

MS-Excel

DataGridView

Hi!

Error occured in my codes.

Is there a way to cast DataTable to Excel.DataTable?

Here is the codes for reference:

Dim f As New FolderBrowserDialog

        Try
            If f.ShowDialog() = DialogResult.OK Then
                'This section help you if your language is not English.
                System.Threading.Thread.CurrentThread.CurrentCulture = _
                System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                Dim oApp As Excel.Application
                oExcel = CreateObject("Excel.Application")
                oApp = GetObject(, "Excel.Application")
                oBook = oExcel.Workbooks.Add(Type.Missing)
                oSheet = oBook.Worksheets(1)

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

                'Fill data to datatable
                Dim DTB As DataTable = New System.Data.DataTable
                grdvwAmortization.DataSource = DTB

                'Dim x As New BindingSource()
                'Dim ds As New DataSet()
                'Dim dt As DataTable = ds.Tables
                'x = DirectCast(grdvwAmortization.DataSource, BindingSource)
                'ds = DirectCast(x.DataSource, DataSet)
                'dt = DirectCast(x.DataSource, DataTable)
                'ds.Tables.Add(dt)


                'Export the Columns to excel file
                For Each dc In DTB.Columns
                    colIndex = colIndex + 1
                    oSheet.Cells(1, colIndex) = dc.ColumnName
                Next

                'Export the rows to excel file
                For Each dr In DTB.Rows
                    rowIndex = rowIndex + 1
                    colIndex = 0
                    For Each dc In DTB.Columns
                        colIndex = colIndex + 1
                        oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                    Next
                Next

                'Set final path
                Dim fileName As String = "\ExportedAuthors" + ".xls"
                Dim finalPath = f.SelectedPath + fileName
                oSheet.Columns.AutoFit()
                'Save file in final path
                oBook.SaveAs(finalPath, XlFileFormat.xlWorkbookNormal, Type.Missing, _
                Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

                'Release the objects
                ReleaseObject(oSheet)
                oBook.Close(False, Type.Missing, Type.Missing)
                ReleaseObject(oBook)
                oExcel.Quit()
                ReleaseObject(oExcel)
                'Some time Office application does not quit after automation: 
                'so i am calling GC.Collect method.
                GC.Collect()

                MessageBox.Show("Export done successfully!")

            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
        End Try

Thank you so much!
This is the last error for my codes.