Problem with Microsoft.Office.Interop.Excel

Question

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.

Answer 1

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.

Answer 2

Hello @sem

Check out the following on GitHub I put together a simple code sample.

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