none
Unable to cast Object ..error while using VS2013 , MSOffice 2010 RRS feed

  • Question

  • Hello guys, sorry i am just a beginner when it comes to programming in VB.NET. i am developing a simple data management application for my personal use , part of the functions i want is to be able to export data to excel from the datagrid view. However whenever i run the particular event (by clickin the button) i always get this error

    " Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to class type 'Dstv.Microsoft.Office.Interop.Excel.Application'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."

    i have tried all i could but this error always come up on this line

    xlapp = excel.application

    below is the full code which i got from this platform and most peaople confirmed it to be working.

    Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer

            xlApp = New Excel.Application()
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("Installation")

            For i = 0 To DataGridView2.RowCount - 2
                For j = 0 To DataGridView2.ColumnCount - 1
                    xlWorkSheet.Cells(i + 1, j + 1) = _
                        DataGridView2(j, i).Value.ToString()
                Next
            Next
            For j = 0 To DataGridView2.ColumnCount - 1
                xlWorkSheet.Cells(1, j + 1) = DataGridView2.Columns(j).Name
            Next
            For i = 0 To DataGridView2.RowCount - 1
                For j = 0 To DataGridView2.ColumnCount - 1
                    Dim cell As DataGridViewCell
                    cell = DataGridView2(j, i)
                    xlWorkSheet.Cells(i + 2, j + 1) = cell.Value
                Next
            Next

            Dim filename As String = "Installations -" & Now().ToString("yyyy-MM-dd-HH-mm-ss") & ".xlsx"
            xlWorkSheet.SaveAs(System.Environment.CurrentDirectory & filename)
            xlWorkBook.Close()
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)

            MsgBox("You can find the file in", System.Environment.CurrentDirectory & "installation.xls")
        End Sub

        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub

    Any assistance will be highly appreciated. Funny enough most related error i have come accross is more of registory error but mine seems unique.

    Saturday, May 18, 2019 2:30 PM

All replies

  • As an update i already added reference microsoft excel 14.0 object library ....this is driving me crazy its been over a week that i have been struggling to make this work, i have uninstalled my office reinstalled several times without any luck. 

    Sorry  as a correction to my first post, the error always come up on 

    xlapp = new excel.application() 

    • Edited by DonBishop Saturday, May 18, 2019 2:34 PM
    Saturday, May 18, 2019 2:33 PM
  • If the error is on xlapp = new excel.application()  

    Try the following (which is only to test creating the application object)

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Module OpenWorkSheets2
        Public Sub OpenExcel1(ByVal FileName As String, ByVal SheetName As String)
            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
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = True
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
                    Console.WriteLine("Found sheet")
    
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            Next
    
        End Sub
        Private Sub ReleaseComObject(ByVal obj As Object)
            Try
                If obj IsNot Nothing Then
                    Marshal.ReleaseComObject(obj)
                    obj = Nothing
                End If
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    
    End Module
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 18, 2019 4:08 PM
    Moderator