question

RashmiGupta-7321 avatar image
0 Votes"
RashmiGupta-7321 asked ·

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

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?




windows-formsoffice-vba-devdotnet-runtime-framework
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Karen certainly has a interesting approach!

If you using office x64, then Access/ACE x64 should work, and should work regardless of the version of office install (only caveat is that Access is also x64).

Only time this house of cards should fall down is if user(s) have office x32 also installed. That's going to be problematic.

I also don't see any Access/ACE code in your example? Did not think an Excel save uses Access/ACE.

But any x64 bit version of ACE should work. Perhaps only that some x32 bit version of ACE is floating around on that machine?

As long as you just use the oleDB provider for ACE, then it should not matter if they are using ACE x64 from 2010, 2013, or 2016. You certainly don't want a existing and hard coded reference to a given version of ACE/office here - but you can safe use an interop reference for this.

So no hard coded reference to a given version of Ace? Correct?

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

0 Votes 0 ·
karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered ·

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

77464-f1.png

77465-f2.png



f1.png (31.9 KiB)
f2.png (55.5 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RashmiGupta-7321 avatar image
0 Votes"
RashmiGupta-7321 answered ·

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?

· 4 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, I created a second project, same code but different version of SpreadSheetLight. See this readme file.

77449-f1.png


1 Vote 1 ·
f1.png (72.6 KiB)

Hi Mam,
I appreciate your efforts.
I understoood your solution & it is working fine, but, my boss told me today is that we cannot use another library, we have to use Excel for this functionality. So, I need a
way that I can use Microsoft office 365 to export the data from datagridview to excel sheet with access database driver 2016. I tried to find a documentation for this but
did not find any.
So, can you help me this way ?

0 Votes 0 ·
Castorix31 avatar image Castorix31 RashmiGupta-7321 ·

we have to use Excel for this functionality.

You can try to use Excel with other native drivers (OleDB, DAO, ODBC, ...)

I had this same error (TYPE_E_ELEMENTNOTFOUND) with Office 2016 and the only way to fix it was with Office Quick Repair (or you would have to trace all registry changes that it does to do them by code...)

(I just did a quick test with DAO (dao360.dll) and it works on my configuration (I can create a random Excel file))



1 Vote 1 ·

I wondering if some additonal details are missing here.

Automating that copy of Excel and doing a save should not effect nor trigger a reference to ACE. is there some VBA or something else going on in that Excel sheet we don't know about?

If you can open that Excel sheet - do a save as, and it works? Then your code should work. I don't see where in your posted code a dependency is occurring to ACE?

Is there additonal code here using ACE? if Excel can open the file and do a save-as, then your automation code should be able to do the same.

We are missing some detail here??? I was not aware that a simple save in Excel requires some dependency on Excel? Can you confirm that when ACE is broken that manually opening that Excel sheet and doing a save as ALSO does not work?

I don't see why a simple save in Excel would need ACE unless some REALLY big detail is being left out or is missing here. Is there a referance to ACE in this project?

0 Votes 0 ·
DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered ·

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.


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Thanks for replying.
I have tried this code before and after executing line
xlexcel = New Excel.Application()
Visual Studio gives same exception as reported in the main question. After applying quick repair on Office, it works well but I don't want to do that. So, my problem is still
unsolved.
Anyway, thanks for paying attention.

0 Votes 0 ·