Problem with Microsoft.Office.Interop.Excel

sem 21 Reputation points
2020-12-17T15:15:15.813+00:00

Hello,

I have simple code to open a excel.
But I couldn't open it..
I googled about this interop problem and tried every ways,
but I didn't get anything..
I use 2019 VB.NET and 2019 MS Office.
Please, help me to fix it.

49224-image.png

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,567 questions
0 comments No comments
{count} votes

Accepted answer
  1. Xingyu Zhao-MSFT 5,356 Reputation points
    2020-12-18T06:23:58.507+00:00

    Hi @sem ,
    I install Microsoft.Office.Interop.Excel on nuget package.
    The following code works for me.

    Imports Microsoft.Office.Interop  
    Public Class Form1  
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
            Dim objApp As Excel.Application  
            Dim objBook As Excel.Workbook  
            Dim objSheet As Excel.Worksheet  
            Dim misValue As Object = System.Reflection.Missing.Value  
      
            objApp = New Excel.Application  
            objBook = objApp.Workbooks.Add(misValue)  
            objSheet = CType(objBook.Sheets("Sheet1"), Excel.Worksheet)  
    
            ' Do something...'  
            objSheet.Cells(2, 1) = "value"  
      
            Dim savePath As String = "your path"  
            objSheet.SaveAs(savePath)  
            objBook.Close()  
            objApp.Quit()  
        End Sub  
    End Class  
    

    Hope it could be helpful.

    Best Regards,
    Xingyu Zhao
    *
    If the answer 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 additional answer

Sort by: Most helpful
  1. Karen Payne MVP 35,031 Reputation points
    2020-12-17T16:34:01.417+00:00

    Hello @sem

    Check out the following on GitHub I put together a simple code sample.
    49147-a1.png

    Backend code

    Option Strict On  
    Option Infer On  
    Imports Excel = Microsoft.Office.Interop.Excel  
    Imports System.Runtime.InteropServices  
    Public Class ExcelOperations  
      
        Public Function GetSheets(fileName As String) As List(Of String)  
      
            Dim sheetNames As New List(Of String)  
            Dim success As Boolean = True  
      
            If Not IO.File.Exists(fileName) Then  
                Dim ex As New Exception("Failed to locate '" & fileName & "'")  
                Throw ex  
            End If  
      
            Dim xlApp As Excel.Application = Nothing  
            Dim xlWorkBooks As Excel.Workbooks = Nothing  
            Dim xlWorkBook As Excel.Workbook = Nothing  
            Dim xlActiveRanges As Excel.Workbook = Nothing  
            Dim xlNames As Excel.Names = Nothing  
            Dim xlWorkSheets As Excel.Sheets = Nothing  
      
            Try  
      
                xlApp = New Excel.Application  
                xlApp.DisplayAlerts = False  
                xlWorkBooks = xlApp.Workbooks  
                xlWorkBook = xlWorkBooks.Open(fileName)  
      
                xlActiveRanges = xlApp.ActiveWorkbook  
                xlNames = xlActiveRanges.Names  
      
                xlWorkSheets = xlWorkBook.Sheets  
      
                For index As Integer = 1 To xlWorkSheets.Count  
      
                    Dim currentSheet As Excel.Worksheet = CType(xlWorkSheets(index), Excel.Worksheet)  
                    sheetNames.Add(currentSheet.Name)  
                    Marshal.FinalReleaseComObject(currentSheet)  
                    currentSheet = Nothing  
      
                Next  
      
                xlWorkBook.Close()  
                xlApp.UserControl = True  
                xlApp.Quit()  
      
            Catch ex As Exception  
                success = False  
            Finally  
      
                If Not xlWorkSheets Is Nothing Then  
                    Marshal.FinalReleaseComObject(xlWorkSheets)  
                    xlWorkSheets = Nothing  
                End If  
      
                If Not xlNames Is Nothing Then  
                    Marshal.FinalReleaseComObject(xlNames)  
                    xlNames = Nothing  
                End If  
      
                If Not xlActiveRanges Is Nothing Then  
                    Marshal.FinalReleaseComObject(xlActiveRanges)  
                    xlActiveRanges = Nothing  
                End If  
                If Not xlActiveRanges Is Nothing Then  
                    Marshal.FinalReleaseComObject(xlActiveRanges)  
                    xlActiveRanges = Nothing  
                End If  
      
                If Not xlWorkBook Is Nothing Then  
                    Marshal.FinalReleaseComObject(xlWorkBook)  
                    xlWorkBook = Nothing  
                End If  
      
                If Not xlWorkBooks Is Nothing Then  
                    Marshal.FinalReleaseComObject(xlWorkBooks)  
                    xlWorkBooks = Nothing  
                End If  
      
                If Not xlApp Is Nothing Then  
                    Marshal.FinalReleaseComObject(xlApp)  
                    xlApp = Nothing  
                End If  
            End Try  
      
            Return sheetNames  
      
        End Function  
      
    End Class  
    

    Frontend code

    Imports System.IO  
      
    Public Class Form1  
        Private Sub OpenButton_Click(sender As Object, e As EventArgs) Handles OpenButton.Click  
      
            Dim fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel1.xlsx")  
      
            If File.Exists(fileName) Then  
                Dim ops = New ExcelOperations  
                Dim sheets = ops.GetSheets(fileName)  
                ListBox1.DataSource = Nothing  
                ListBox1.DataSource = sheets  
            Else  
                MessageBox.Show($"{fileName} not found")  
            End If  
      
        End Sub  
    End Class