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,778 questions
0 comments No comments
{count} votes

Accepted answer
  1. Xingyu Zhao-MSFT 5,371 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,551 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  
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.