How to run Microsoft Office Version : 18.2008.12711.0 with accessdatabaseengine_X64 in .NET Framework 4.8?

Question

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 next to,
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 datadase driver can work properly, but I am still getting this issue.

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

Answer 1

Hello,

Rather than use Office automation, a cleaner method is to work with OpenXML for Excel using SpreadSheetLight which is a free library installed using NuGet package manager inside of Visual Studio.

The following code sample compiles as x86 or x64, uses a DataTable as the DataSource for a DataGridView (which is better than populating without a data source. Where the data comes from to populate is not a concern, could comes from a text file, tables in a database etc, all that matters is having the DataTable populated.

Full source can be found in the following GitHub repository so you can look at the code, if you decide to use the code copy and paste code into your project in regards to the file ExcelOperations.vb and install the required NuGet packages (there are instructions on the main page for this)

Backend class for Excel operation

Imports SpreadsheetLight  
  
Public Class ExcelOperations  
  
    ''' <summary>  
    ''' Export a DataTable to Excel  
    ''' </summary>  
    ''' <param name="pFileName">path and file name to save too, path is optional</param>  
    ''' <param name="pSheetName">Name of sheet</param>  
    ''' <param name="pDataTable">Populated DataTable</param>  
    ''' <param name="pColumnHeaders">True for column names as first row, false no column names</param>  
    Public Shared Sub SimpleExportRaw(pFileName As String, pSheetName As String, pDataTable As DataTable, pColumnHeaders As Boolean)  
  
        Using doc As New SLDocument()  
  
            doc.SelectWorksheet(pSheetName)  
  
            doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)  
  
  
            Dim style As New SLStyle With {.FormatCode = "MM/dd//yyyy"}  
  
            ' Format modified date column  
            doc.SetColumnStyle(11, style)  
  
            style.Font.Bold = True  
            doc.SetRowStyle(1, 1, style)  
  
            Dim stats = doc.GetWorksheetStatistics()  
  
            ' auto fit all columns  
            doc.AutoFitColumn(1, stats.EndColumnIndex)  
  
            ' original default name is Sheet1, let's change it to the name in pSheetName  
            doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, pSheetName)  
  
            doc.SaveAs(pFileName)  
  
        End Using  
  
    End Sub  
End Class  
  

Button click event in form

Private Sub ExportToExcelButton_Click(sender As Object, e As EventArgs) Handles ExportToExcelButton.Click  
    Dim dt = CType(DataGridView1.DataSource, DataTable)  
    ExcelOperations.SimpleExportRaw("Customers.xlsx", "Customers", dt, True)  
    MessageBox.Show("Exported")  
End Sub  

Answer 2

Hi,
Thanks for paying attention.
I tried to implement your solution but when I try to install SpreadSheetLight, it gives an error as:

"Could not install package 'SpreadsheetLight 3.5.0'. You are trying to install this package into a project that targets '.NETFramework,Version=v4.8', but the package does not contain any assembly references or content files that are compatible with that framework. For more information, contact the package author."

I have to run my project in .Net Framework 4.8. What should I do?

Answer 3

Hi RashmiGupta-7321,
You can try to use DataGridView.GetClipboardContent method to copy the data of the datagridview, and then paste it into Excel.
More details you can refer to Jake's code example in this thread.
And here is VB code:

Private Sub copyAlltoClipboard()  
        DataGridView1.SelectAll()  
        Dim dataObj As DataObject = DataGridView1.GetClipboardContent()  
        If dataObj IsNot Nothing Then Clipboard.SetDataObject(dataObj)  
    End Sub  
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
        copyAlltoClipboard()  
        Dim xlexcel 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 = Missing.Value  
        xlexcel = New Excel.Application()  
        xlexcel.Visible = True  
        xlWorkBook = xlexcel.Workbooks.Add(misValue)  
        xlWorkSheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)  
        Dim CR As Excel.Range = CType(xlWorkSheet.Cells(1, 1), Excel.Range)  
        CR.[Select]()  
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, True)  
    End Sub  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.