question

sem-3806 avatar image
0 Votes"
sem-3806 asked ·

Problem with Microsoft.Office.Interop.Excel

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


dotnet-visual-basicoffice-vba-dev
image.png (100.1 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

XingyuZhao-MSFT avatar image
0 Votes"
XingyuZhao-MSFT answered ·

Hi @sem-3806 ,
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.



· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your answer. I have just installed it, but it still doesn't work.. I use currently MS Office 2019 version. The NuGet Package seems to support only for MS Office 2013 or it doen't matter of version?

0 Votes 0 ·

I reinstalled MS Office completely. then it works, good! Thanks

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered ·

Hello @sem-3806

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



a1.png (11.5 KiB)
· 7 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your detail source code.
I tried your code, but it didn't still work.
First I would like to know, which references did you add?
I added Microsoft.Office.Core(Microsoft Office 16.0 Object Library) and Microsoft.Office.Interop.Excel(C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c).

0 Votes 0 ·

In regards to not working, I noticed in your screenshot (and it's wise to post code in a code block) that you didn't specify a file name, if you did I can't see it and that would be one reason why the code fails.

I used one reference (and earlier version as thats what I have but should not matter)

C:\Program Files (x86)\Microsoft Visual Studio\Shared\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll

0 Votes 0 ·

When I run your code, I didn't find any errors. It didn't just happen anything. I think that your code seems to be clear. The problem could be Microsoft.Office.Interop.Excel, and I have to find this DLL file in my C driver. And I found 1 file, which is located in the Microsoft Office\root\Office16\ADDINS. I tried whit it also but it fails..
[1]


[1]: /answers/storage/temp/49170-screenshot-2020-12-17-204805.jpg

0 Votes 0 ·
Show more comments