I am a newbie to this platform and also to running MS Office applications with .Net. I am trying to export data from datagridview to excel sheet on click of a button. Here's my code :
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
For i = 0 To DataGrdView.RowCount - 2
For j = 0 To DataGrdView.ColumnCount - 1
For k As Integer = 1 To DataGrdView.Columns.Count
xlWorkSheet.Cells(1, k) = DataGrdView.Columns(k - 1).HeaderText
xlWorkSheet.Cells(i + 2, j + 1) = DataGrdView(j, i).Value.ToString()
Next
Next
Next
xlWorkSheet.SaveAs("E:\Student information.xlsx")
After executing the line, xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
Visual Studio reports an exception as :
"An unhandled exception of type 'System.InvalidCastException' occurred in Student Information System.exe
Additional information: Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Element not found. (Exception from HRESULT: 0x8002802B (TYPE_E_ELEMENTNOTFOUND))."
This error is coming because I am using AccessDatabaseEngine_X64 which is released in 2016, with MS Office 365 Version : 18.2008.12711.0. with .Net Framework 4.8.
I know that I can manually repair the office and problem will be solved but I don't want my application's user to repair the office and handle this error manually instead I want to provide a functionality that automatically handles this issue and give appropriate results.
I tried deleting key Computer\HKEY_CLASSES_ROOT\TypeLib{00020813-0000-0000-C000-000000000046}\1.9 in Registry Editor but this didn't work.
I also made sure that all my Office apps are running in 64 bit mode so that database driver can work properly, but I am still getting this issue.
Which changes should I implement to get rid of this trouble?