.net & Excel - From Office 2007 to 2013

Question

so, our IT department upgraded everybody from Office 2007 to Office 2013, and many of my hard work has to be redone. (VS 2013 Express, framework 4.5)

The error I am getting is:

"Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Range'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00020846-0000-0000-C000-000000000046}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155”

I understand that this is caused by the interop new dll version etc, but I have spent hours and hours reading about it without finding anything useful. It crashes the first "xlws.Cells(i, j) = dt.Rows(i - 1).Item(j-1).ToString()"

I did find some workarounds and finally managed to get it almost working, but it feels so much more complicated than it used to be I am still struggling with the ranges and sorting.

If someone could share a simple code allowing to:

Open Excel
Create a worksheet
Export values from a datatable to the worksheet
Define a range
Sort the range

I would be really thankful!

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = CreateObject("Excel.application")
xlApp.Visible = False
xlApp.DisplayAlerts = False
xlWorkBook = xlApp.Workbooks.Add(misValue)

Dim myrange As Excel.Range
Dim xlws As Excel.Worksheet
xlws = xlwb.Worksheets.Add(After:=xlwb.Worksheets(xlwb.Worksheets.Count))
xlws.Name = “name”

For i = 1 To dt.Rows.Count
    For j = 1 To dt.Columns.Count
        xlws.Cells(i, j) = dt.Rows(i - 1).Item(j-1).ToString()
    Next
Next

xlws.Columns.AutoFit()

myrange = xlws.UsedRange
myrange.Select()
myrange.Sort(Key1:=myrange.Range("M1"), Order1:=Excel.XlSortOrder.xlAscending,     Orientation:=Excel.XlSortOrientation.xlSortColumns)
myrange = xlws.Cells(2, 1)
myrange.Select()

xlWorkBook.Worksheets("Sheet1").Delete()
xlWorkBook.Worksheets("Sheet2").Delete()
xlWorkBook.Worksheets("Sheet3").Delete()
xlWorkBook.Worksheets("name").select()

xlWorkBook.SaveAs(folder_str & Format(Now, "MMddyyyy").ToString & " - " & “name” & ".xlsx")

xlWorkBook.Close()
xlApp.Quit()

Marshal.ReleaseComObject(xlws)
Marshal.ReleaseComObject(xlWorkBook)
Marshal.ReleaseComObject(xlApp)

Eugene Astafiev Eugene Astafiev · Accepted Answer · 2015-01-12T18:29:54

The Cells property of the Worksheet class returns an instance of the Range class. So you are trying to assign a text to a range object which is not possible.

In general, to avoid such issue I'd suggest breaking the chain of calls (multiple dots in the single line of code) and declaring each property or method call on a separate line of code.

You may find the How to automate Microsoft Excel from Visual Basic and Excel Automation from Ron de Bruin links helpful. There you will find a sample code.

.net & Excel - From Office 2007 to 2013

1 Answers