none
Visual Studio 2017 & Office 365 RRS feed

  • Question

  • Hi all,

    I am currently creating a program in Visual Studio 2017, and recently updated to Office 365. Prior to updating, my application was working fine, however, after updating, I am having trouble with my Microsoft Excel imports. My program is taking a file out of excel, running a macro, and converting it to a PDF. When I go to add my Microsoft Excel 16.0 Object Library reference, the following popup returns, "A reference to 'Microsoft Excel 16.0 Object Library' could not be added. A reference to this type library already exists. You must remove the reference 'Microsoft.Office.Interop.Excel' before adding this one." This is strange to me since I have deleted all references to the microsoft.office.interop.excel. 

    Please advise.

    Thanks,

    Lyvia

    Monday, April 29, 2019 7:59 PM

Answers

  • Hi,

    You can try to click the “Show All Files” button in Solution Explorer,open the “Refenerences” node,see the existence of Microsoft.Office.Interop.Excel, if there is, delete it.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Lyvia H Wednesday, May 1, 2019 6:05 PM
    Tuesday, April 30, 2019 1:45 AM
  • Hello,

    I would suggest not using ApplicationClass. Have seen over the years many developers having issues.

    Could you try the following which uses the Application class. Flow of code, having an existing Excel file with a Sheet2 open the Excel file, select Sheet2 then close all Excel objects fully releasing all memory used by the Excel objects.

    Private Sub Button1_Click(sender As Object, e As EventArgs) _
        Handles Button1.Click
    
        Dim fileName = Path.Combine(
        AppDomain.CurrentDomain.BaseDirectory, "Excel1.xlsx")
    
        Dim sheetName = "Sheet2"
    
        If File.Exists(fileName) Then
            Dim ops As New ExcelOperations
            ops.OpenExcel(fileName, sheetName)
        End If
    
    End Sub

    Excel class

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Public Class ExcelOperations
        ''' <summary>
        ''' Open Excel file to a specific WorkSheet
        ''' </summary>
        ''' <param name="pFileName">Path and file name</param>
        ''' <param name="pSheetName">Sheet to work on</param>
        Public Sub OpenExcel(pFileName As String, pSheetName As String)
    
            Dim proceed = 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
    
            xlApp = New Excel.Application With {
                .DisplayAlerts = False
            }
    
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(pFileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For sheetIndex As Integer = 1 To xlWorkSheets.Count
    
                xlWorkSheet = CType(xlWorkSheets(sheetIndex), Excel.Worksheet)
    
                If xlWorkSheet.Name = pSheetName Then
                    proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            If proceed Then
                Console.WriteLine($"Found and using {pSheetName}")
            Else
                Console.WriteLine($"Did not find {pSheetName}")
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
    
            ' ReSharper disable once LocalizableElement
            Console.WriteLine("Objects are fully released now")
    
        End Sub
        Private Sub ReleaseComObject(sender As Object)
            Try
                Marshal.ReleaseComObject(sender)
                sender = Nothing
            Catch ex As Exception
                sender = Nothing
            End Try
        End Sub
    End Class
    


    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

    • Marked as answer by Lyvia H Wednesday, May 1, 2019 6:05 PM
    Wednesday, May 1, 2019 2:10 PM
    Moderator

All replies

  • Hi,

    You can try to click the “Show All Files” button in Solution Explorer,open the “Refenerences” node,see the existence of Microsoft.Office.Interop.Excel, if there is, delete it.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Lyvia H Wednesday, May 1, 2019 6:05 PM
    Tuesday, April 30, 2019 1:45 AM
  • Hi Alex, thank you for your help. I am still having an issue with declaring a new application class. The error is saying "Reference to class 'ApplicationClass' is not allowed when its assembly is configured to embed interop types".
    Wednesday, May 1, 2019 12:02 PM
  • Hello,

    I would suggest not using ApplicationClass. Have seen over the years many developers having issues.

    Could you try the following which uses the Application class. Flow of code, having an existing Excel file with a Sheet2 open the Excel file, select Sheet2 then close all Excel objects fully releasing all memory used by the Excel objects.

    Private Sub Button1_Click(sender As Object, e As EventArgs) _
        Handles Button1.Click
    
        Dim fileName = Path.Combine(
        AppDomain.CurrentDomain.BaseDirectory, "Excel1.xlsx")
    
        Dim sheetName = "Sheet2"
    
        If File.Exists(fileName) Then
            Dim ops As New ExcelOperations
            ops.OpenExcel(fileName, sheetName)
        End If
    
    End Sub

    Excel class

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Public Class ExcelOperations
        ''' <summary>
        ''' Open Excel file to a specific WorkSheet
        ''' </summary>
        ''' <param name="pFileName">Path and file name</param>
        ''' <param name="pSheetName">Sheet to work on</param>
        Public Sub OpenExcel(pFileName As String, pSheetName As String)
    
            Dim proceed = 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
    
            xlApp = New Excel.Application With {
                .DisplayAlerts = False
            }
    
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(pFileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For sheetIndex As Integer = 1 To xlWorkSheets.Count
    
                xlWorkSheet = CType(xlWorkSheets(sheetIndex), Excel.Worksheet)
    
                If xlWorkSheet.Name = pSheetName Then
                    proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            If proceed Then
                Console.WriteLine($"Found and using {pSheetName}")
            Else
                Console.WriteLine($"Did not find {pSheetName}")
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
    
            ' ReSharper disable once LocalizableElement
            Console.WriteLine("Objects are fully released now")
    
        End Sub
        Private Sub ReleaseComObject(sender As Object)
            Try
                Marshal.ReleaseComObject(sender)
                sender = Nothing
            Catch ex As Exception
                sender = Nothing
            End Try
        End Sub
    End Class
    


    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

    • Marked as answer by Lyvia H Wednesday, May 1, 2019 6:05 PM
    Wednesday, May 1, 2019 2:10 PM
    Moderator