none
Vb.Net and Excel automation error RRS feed

  • Question

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

    This has occurred since installing MS office f2016 (previous version 2010 worked fine).  I have tried the regsvr32 on the dll but it makes no difference. I have re-installed VB Express 2017 and Office

    Tuesday, January 16, 2018 8:16 PM

Answers

  • https://support.microsoft.com/en-gb/help/302094/how-to-automate-excel-from-visual-basic--net-to-fill-or-to-obtain-data 

    MVS  Community 2017 ver 15.5.3 (this is the latest I have reinstalled)

    Microsoft.Office.Interop.Excel.15.0.4795.1000

    Microsift Excel 16.0 Object library Excel.EXE ver 1.9 is in the type Libraries

    Thanks 

    Wednesday, January 17, 2018 12:33 PM

All replies

  • Did you update your reference in the project?  Office.Interop ties to fairly specific versions of Office.  You can use ExcelDNA from NuGet for a version agnostic Excel automation assembly.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, January 16, 2018 8:25 PM
    Moderator
  • I have built a new project and that is the same error I get in my developed projects. It appears to me, to be something that has been been delivered by Microsoft and is not working.

    Thanks


    Tuesday, January 16, 2018 10:00 PM
  • So what version of the Primary Interop assemblies are you using?  Just because you created a new project it doesn't mean that you didn't add the same incompatible reference.  Office 2010 has a different assembly than Office 2013/2016.  You should be using version 15 of Office.Interop.Excel.

    Here's what the reference list looks like in a new Excel 2013-2016 Add-In project:


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, January 16, 2018 11:05 PM
    Moderator
  • Office 2016 installs the latest version Microsoft.Office.Interop.Excel.15.0.4795.1000. The code I am using is a MS example for Excel automation. All my problems began when I subscribed to the MS Office updates. It is a VB.net add-in object, in VB.Net 2017 express it points to Excel.exe which fails. These are all built in to the product.
    Wednesday, January 17, 2018 10:16 AM
  • Can you provide the link to the example you used?  Also what version (specific release version under Help - About) of Visual Studio are you using?

    I have Office 2016 (with updates) Visual Studio 2017 v.15.3.1 (which is a couple service releases behind 15.5.4).  When I create a new project and select the Excel Add-In template from the Office category, the default reference used is the 15.0.0.0 as shown above.

    If your reference path is showing a higher version number, can you also please include the path to the assembly?

    I haven't heard of this issue and can't reproduce it, but if it is related to a specific version of VS then we can get it reported and potentially fixed (if it is indeed a VS issue).


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, January 17, 2018 10:31 AM
    Moderator
  • Hello,

    It's very possible that the issue is with your code in that something has changed from  2010 to 2016 and that in the 2016 version of Excel an Interface has change thus not valid anymore.

    You could specify in words or in code what exactly is failing which may or may not help depending on the complexity of the project and someone else's ability to reproduce your environment.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 17, 2018 11:21 AM
    Moderator
  • https://support.microsoft.com/en-gb/help/302094/how-to-automate-excel-from-visual-basic--net-to-fill-or-to-obtain-data 

    MVS  Community 2017 ver 15.5.3 (this is the latest I have reinstalled)

    Microsoft.Office.Interop.Excel.15.0.4795.1000

    Microsift Excel 16.0 Object library Excel.EXE ver 1.9 is in the type Libraries

    Thanks 

    Wednesday, January 17, 2018 12:33 PM
  • I would recommend running the Office install and selecting the Repair option. This doesn't sound like a code fix to me.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 17, 2018 12:55 PM
  • https://support.microsoft.com/en-gb/help/302094/how-to-automate-excel-from-visual-basic--net-to-fill-or-to-obtain-data 

    MVS  Community 2017 ver 15.5.3 (this is the latest I have reinstalled)

    Microsoft.Office.Interop.Excel.15.0.4795.1000

    Microsift Excel 16.0 Object library Excel.EXE ver 1.9 is in the type Libraries

    Thanks 

    The first thing I saw when looking at this example was it's old/out-dated. They are using a reference for Excel from the COM tab to add the reference where the better way is through Assemblies

    Then for iterating a range as an array (something I had laying around) we do something like this.

    Option Strict On
    Imports Excel = Microsoft.Office.Interop.Excel
    Module ExcelDemoIteratingData_2
        Public Sub DemoGettingDates()
    
    
            Dim dt As DataTable = OpenExcelAndIterate(
                IO.Path.Combine(
                    AppDomain.CurrentDomain.BaseDirectory,
                    "GetDatesFromB.xlsx"),
                "Sheet1",
                "B1",
                "B10")
    
            Dim SomeDate As Date = #12/1/2013#
    
            Dim Results =
                (
                    From T In dt
                    Where Not IsDBNull(T.Item("SomeDate")) AndAlso T.Field(Of Date)("SomeDate") = SomeDate
                    Select T
                ).ToList
    
            If Results.Count > 0 Then
                For Each row As DataRow In Results
                    Console.WriteLine("Row [{0}] Value [{1}]",
                                      row.Field(Of Integer)("Identifier"),
                                      row.Field(Of Date)("SomeDate").ToShortDateString)
                Next
            End If
    
        End Sub
        Public Function OpenExcelAndIterate(
            ByVal FileName As String,
            ByVal SheetName As String,
            ByVal StartCell As String,
            ByVal EndCell As String) As DataTable
    
            Dim dt As New DataTable
    
            If IO.File.Exists(FileName) Then
    
                Dim Proceed As Boolean = False
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
    
                '
                ' For/Next finds our sheet
                '
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
    
                If Proceed Then
    
                    dt.Columns.AddRange(
                        New DataColumn() _
                        {
                            New DataColumn With {.ColumnName = "Identifier", .DataType = GetType(Int32), .AutoIncrement = True, .AutoIncrementSeed = 1},
                            New DataColumn With {.ColumnName = "SomeDate", .DataType = GetType(Date)}
                        }
                    )
    
                    Dim xlUsedRange = xlWorkSheet.Range(StartCell, EndCell)
    
                    Try
    
                        Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
    
                        If ExcelArray IsNot Nothing Then
                            ' Get bounds of the array.
                            Dim bound0 As Integer = ExcelArray.GetUpperBound(0)
                            Dim bound1 As Integer = ExcelArray.GetUpperBound(1)
    
                            For j As Integer = 1 To bound0
                                If (ExcelArray(j, 1) IsNot Nothing) Then
                                    dt.Rows.Add(New Object() {Nothing, ExcelArray(j, 1)})
                                Else
                                    dt.Rows.Add(New Object() {Nothing, Nothing})
                                End If
                            Next
                        End If
                    Finally
                        ReleaseComObject(xlUsedRange)
                    End Try
    
                Else
                    MessageBox.Show(SheetName & " not found.")
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlCells)
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
            End If
    
            Return dt
    
        End Function
        Private Sub ReleaseComObject(ByVal sender As Object)
            Try
                If sender IsNot Nothing Then
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sender)
                    sender = Nothing
                End If
            Catch ex As Exception
                sender = Nothing
            End Try
        End Sub
    End Module
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 17, 2018 1:00 PM
    Moderator
  • https://support.microsoft.com/en-gb/help/302094/how-to-automate-excel-from-visual-basic--net-to-fill-or-to-obtain-data 

    MVS  Community 2017 ver 15.5.3 (this is the latest I have reinstalled)

    Microsoft.Office.Interop.Excel.15.0.4795.1000

    Microsift Excel 16.0 Object library Excel.EXE ver 1.9 is in the type Libraries

    Thanks 


    As Karen said, there's your problem.  Outdated example and wrong assembly reference.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, January 17, 2018 1:06 PM
    Moderator
  • when adding via assemblies in the sample code I submitted it errors Type 'Excel Application' is not defined.  It no longer reconciles it even if I change it back.

    Thank you for the code, am I to do something with it?



    Wednesday, January 17, 2018 4:05 PM
  • By that simple exercise the sample code should work but it does not. I use VB.Net Community freebie, perhaps the Professional version works with the latest Office, however I am reluctant to buy the product to find it doesn't.

    Whatever method is used to load the reference should not determine if it works.

    There has not been a definitive answer as yet,

    Did you try the sample code I sent you?

    Thanks

    Wednesday, January 17, 2018 4:12 PM
  • when adding via assemblies in the sample code I submitted it errors Type 'Excel Application' is not defined.  It no longer reconciles it even if I change it back.

    Than you for the code, am I to do something with it

    Not sure why Excel.Application is not defined, the example I provided uses Excel.Application and have written more than 20 code samples uses the assemblies I indicated.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 17, 2018 4:21 PM
    Moderator
  • I have done that but no change. I don't think it is a code issue but then why does the code not work with the latest version of Office, is the question. 
    Wednesday, January 17, 2018 4:34 PM
  • If you try late binding to the Excel.Application object and it fails then you have an Office install issue and need to repair it. This would be one way of testing the Office install without specifying the interop libraries:

    Dim excelObject as Object excelObject = CreateObject("Excel.Application")
    excelObject.Quit()



    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 17, 2018 4:38 PM
  • I have done that but no change. I don't think it is a code issue but then why does the code not work with the latest version of Office, is the question. 

    Try the late binding code I just posted. That method should work if Office is installed properly. If that works then it's probably just a matter of getting the interop assemblies set up correctly.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 17, 2018 4:44 PM
  • By that simple exercise the sample code should work but it does not. I use VB.Net Community freebie, perhaps the Professional version works with the latest Office, however I am reluctant to buy the product to find it doesn't.

    Whatever method is used to load the reference should not determine if it works.

    There has not been a definitive answer as yet,

    Did you try the sample code I sent you?

    Thanks

    No, the sample code to which you posted a link should NOT work with the newer versions of Office.  Those are the old Office PIAs.  You need to use the new assemblies as Karen has shown.

    There is no Excel._Workbook object in the new assemblies (its just Excel.Workbook).  So Office 2013/2016 is not going to work with the old assemblies and you won't be able to just update the assembly without refactoring the code to work with the new assemblies.

    You'll most likely need to create a new version of your application for the new version of Office.

    Note that in this respect there shouldn't be a difference between the capability of VS2017 Community versus Professional unless you don't have the Office New Project templates - then I guess it would be a bit more manual work but should still be doable.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, January 17, 2018 6:03 PM
    Moderator
  • Adding on to Reed's reply, the following MSDN code samples all use the non COM version for working with Excel. If you have one version of Excel and try the code it will work then move to another version it will still work.

    https://code.msdn.microsoft.com/Basics-of-using-Excel-4453945d

    https://code.msdn.microsoft.com/Excel-get-used-rows-and-15b43cb7

    Now if you are working with .xlsx files only there are two libraries worth looking at, SpreadSheetLight which is free and Gembox Spreadsheet (paid for library) where both work without Excel even being installed thus no dependencies like you have now.

    I have several SpreadSheetLight samples in a MSDN code sample. I don't have a Gembox code sample in vb.net, only C# but it's an excellent library.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 17, 2018 7:01 PM
    Moderator
  • Paul,the code works okay

    Wednesday, January 17, 2018 7:10 PM
  • I ran the first link and got the same error
    Wednesday, January 17, 2018 8:24 PM
  • There is no Excel._Workbook object in the new assemblies (its just Excel.Workbook).  So Office 2013/2016 is not going to work with the old assemblies and you won't be able to just update the assembly without refactoring the code to work with the new assemblies.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Excel._Workbook is an Interface and is supported in the Excel PIA (Microsoft.Office.Interop.Excel). Also, I tried the example from the link the OP posted and it works fine for me. Actually, if the PIA is installed it will automatically use it when you add the Microsoft Excel X.0 Object Library COM reference to a project. If the PIA is not installed then a generic interop library (e.g. Interop.Excel.dll) is created instead.

    There's something else going on here, but I'm not sure what it is yet.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 17, 2018 8:37 PM
  • Paul,the code works okay

    After researching this issue a bit more, it could be that references to a version of the Office libraries that is not installed is causing a problem. See if the below link helps (make sure to export any Registry entries before deleting):

    https://jetsupport.jetreports.com/hc/en-us/articles/219403977-Error-Unable-to-cast-COM-object-of-type-


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 17, 2018 9:15 PM
  • I ran the first link and got the same error
    My code sample works fine, there are over 7,000 downloads, zero issues, see the question section.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 18, 2018 12:52 AM
    Moderator
  • I see that they are using the earlier versions of VB.Net which my code also worked on. The Office install would have also been an earlier version. I have currently removed VB.Net 2017 but I don't have the same option with Office. I do have VB2015 on another machine which I will try.

    Thanks

    Thursday, January 18, 2018 7:10 AM