Click here to Skip to main content
14,932,748 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
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.


Which changes should I implement to get rid of this trouble?

What I have tried:

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 datadase driver can work properly, but I am still getting this issue.
Posted
Updated 15-Mar-21 1:16am
v4
Comments
Richard MacCutchan 12-Mar-21 8:49am
   
I do not see any reference to AccessDatabaseEngine_X64 in your code, and I doubt whether that would affect the interface to the Excel namespace.

Also, you should never delete or otherwise modify registry keys that you do not own; especially thos on the ROOT set.

Rather than using Office Interop, use a library which creates an Excel file directly. That way, it doesn't matter which version of Office your users have installed - your code will still work if they don't have Office installed at all.

Previously I would have recommended EPPlus[^]. However, the most up-to-date version is no longer free for "commercial" use[^]. If you don't meet the requirements for the free license, you would need to stick to v4, which is the last truly free version.

Other alternatives include ClosedXML[^], NPOI[^], or The OpenXML SDK[^].

Any of these will allow you to create an Excel spreadsheet on a machine which doesn't have Excel installed.
   
You are trying to use the Excel ApplicationClass to initialise the Application object:
VB
xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass

It should be:
VB
xlApp = New Microsoft.Office.Interop.Excel.Application
   
Comments
Rash24Agg 15-Mar-21 8:04am
   
I changed it as you said and it is still giving same error.
Richard MacCutchan 15-Mar-21 8:25am
   
Sorry, but it works on my system. Although I have Office 2016, not 365.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900