Close Excel - Add-in Express .NET forum

Michael Kaden

Guest

Dear Andrei,

Now that I am on Windows10, Visual Studio 2019 and using WPF Windows it is getting difficult.

I have very simple procedure which opens a new workbook and closes it again. When I register the project or publish it, it runs as intended from Excel. I can run it repeatedly. The task manager shows:

First one instance of Excel when I open Excel
Then the second instance of Excel
then after Workbooks.Close only the first instance
and when I close the Excel there is no instance in the Task Manager There is also no error raised at any time

When I then want to rebuild the project, I get the message

Visual Studio cannot access the project because it is used by another process. Please close running Office applications to continue.

But there is no Office application in the Task manager. I have to reboot to continue.

Here is the code:



    Private Sub Button2_Click(sender As Object, e As System.Windows.RoutedEventArgs)
        Dim xlApp As New Excel.Application
        Dim xlWorkSheet As Excel.Worksheet
        'Create a new file open Dialog
        Dim openfile As New OpenFileDialog
        'Set Up Dialog
        openfile.Title = "Select File"
        openfile.Filter = "Microsoft Excel|*.xls*"
        openfile.ShowDialog()
        txt.Text = openfile.FileName & "  "
        DataFile = openfile.FileName
        xlApp.Workbooks.Open(DataFile)
        xlWorkSheet = CType(xlApp.Sheets(1), Excel.Worksheet)
        xlApp.DisplayAlerts = False
        xlApp.Workbooks.Close()
        xlApp.Quit()
        If xlApp.Workbooks IsNot Nothing Then
            Marshal.ReleaseComObject(xlApp.Workbooks)
        End If
        If xlApp IsNot Nothing Then
            Marshal.ReleaseComObject(xlApp)
        End If
    End Sub

When I step through with debugging, I can see the Excel Instance showing in the Task manager and disappearing again on closing as intended. But I can only do debugging once, on the second attempt I get the message from above.

After many hours of Google research and trying out many different things, unfortunately no success. Can you help me?

Just thinking, that I perhaps should describe the setup better:

I have a ADX COM AddIN installed. With a Ribbon Button I open a WPS Window. On this Window I have "Button2" and Button2_Click above is the event handler.

Perhaps that helps?

Thank you and kind regards

Michael

Michael Kaden

Guest

Hello Andrei,

I did a bit more research this evening and I think I have found the solution. I have to release the Workbook before I close the Application:


        xlApp.Workbooks.Close()

        If xlApp.Workbooks IsNot Nothing Then
            Marshal.ReleaseComObject(xlApp.Workbooks)
        End If

        xlApp.Quit()

        If xlApp IsNot Nothing Then
            Marshal.ReleaseComObject(xlApp)

        End If

So far it looks like this works. Strange behavior though.

Thanks for your continuous support.

kind regards & have a nice Sunday.

Michael

Andrei Smolin

Add-in Express team

Posts: 18817
Joined: 2006-05-11

Hello Michael,

Releasing COM objects is what Office expects from you. In the code fragment above *each* xlApp.Workbooks call creates and leaves unreleased a COM object of the Excel.Workbooks type. You should re-write it as follows:

Private Sub Button2_Click(sender As Object, e As System.Windows.RoutedEventArgs) 
    Dim xlApp As New Excel.Application 
    Dim xlWorkbooks As Excel.Workbooks 
    Dim xlWorkbook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet 
    'Create a new file open Dialog 
    Dim openfile As New OpenFileDialog 
    'Set Up Dialog 
    openfile.Title = "Select File" 
    openfile.Filter = "Microsoft Excel|*.xls*" 
    openfile.ShowDialog() 
    txt.Text = openfile.FileName & "  " 
    DataFile = openfile.FileName 
    xlWorkbooks = xlApp.Workbooks
    xlWorkbook = xlWorkbooks.Open(DataFile) 
    xlWorkSheet = CType(xlApp.Sheets(1), Excel.Worksheet) 
    xlApp.DisplayAlerts = False 
    xlWorkbooks.Close() 
    xlApp.Quit() 
    Marshal.ReleaseComObject(xlWorkSheet) 
    Marshal.ReleaseComObject(xlWorkbook) 
    Marshal.ReleaseComObject(xlWorkbooks) 
    Marshal.ReleaseComObject(xlApp) 
End Sub

Also check https://www.add-in-express.com/creating-addins-blog/2011/11/04/why-doesnt-excel-quit/.

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Hello Andrei,

Thank you for your link to the article about Release. I agree one should have a complete release of all objects.

Unfortunately, when using your code, the problem of NOT being able to rebuild the project after the run is still there. (There is still one instance of EXCEL.EXE in svchost after closing EXCEL).

Just for info, this only happens on my new WPF Windows which has a button to open a new EXCEL instance. All the other new WPF Windows, where I do not open a new EXCEL instance have no problem at all.

I add the WPF Windows in the ADX Class project as I understand to add an additional WPF project to a solution with an ADX Class project is not possible? To add WPF windows directly and not rename a WPF user control I followed the following:

http://www.mobilemotion.eu/?p=1537

This worked very well with all the other new WPF Windows (without...Dim xlApp As New Excel.Application).

As I cannot find anything in the Task Manager, I use Process Explorer.exe to show all running processes.

To get nearer to the problem I built up the code in Button2 line by line:

First trial

Private Sub Button2_Click(sender As Object, e As System.Windows.RoutedEventArgs)
        Dim Halt As Integer = 1
        Dim xlApp As New Excel.Application
        Halt = 2
        Dim xlWorkbooks As Excel.Workbooks
        Halt = 3
        xlWorkbooks = xlApp.Workbooks
        xlWorkbooks.Close()
        xlApp.Quit()
        Marshal.ReleaseComObject(xlWorkbooks)
        Marshal.ReleaseComObject(xlApp)

    End Sub

ProcessExplorer.exe:

Dim xlApp As New Excel.Application makes 1 instance in svchost Path:[Access denied]

Instance is removed from svchost only after Excel is completely closed

Why not on:

      xlWorkbooks.Close()
        xlApp.Quit()
        Marshal.ReleaseComObject(xlWorkbooks)
        Marshal.ReleaseComObject(xlApp)

Without an Excel instance remaining in svchost, here rebuild is possible

Second trial

To make it work (open the Excel file ), I added some of the original commands.

Dim xlApp As New Excel.Application
        Dim xlWorkbooks As Excel.Workbooks
        Dim xlWorkbook As Excel.Workbook     
        Dim openfile As New OpenFileDialog
        openfile.Filter = "Microsoft Excel|*.xls*"
        openfile.ShowDialog()    
        DataFile = openfile.FileName
        xlWorkbooks = xlApp.Workbooks
        xlWorkbook = xlWorkbooks.Open(DataFile)
        xlApp.DisplayAlerts = False
        xlWorkbooks.Close()
        xlApp.Quit()
        Marshal.ReleaseComObject(xlWorkbook)
        Marshal.ReleaseComObject(xlWorkbooks)
        Marshal.ReleaseComObject(xlApp)

Dim xlApp As New Excel.Application makes 1 instance in svchost Path:[Access denied]

The Instance is NOT removed from svchost even after Excel is completely closed

Cannot find EXCEL anywhere, including background processes in the Task manager

As long as there is an instance of EXCEL.EXE in svchost, rebuild is not possible.

If I kill it manually in the Process Explorer.exe everything is back to normal and I can rebuild again.

This in a nutshell is the result of 2 days trial. I googled a lot and tried many alternatives. I could not find anything WPF in your manual.

If I read the following

https://www.add-in-express.com/forum/read.php?FID=5&TID=14672

lets me think that adding WPF Windows to an ADX project is not possible ?

Hope you can help.

Thank you and kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18817
Joined: 2006-05-11

Hello Michael,

Let's get rid of xlApp.DisplayAlerts = False. Start with commenting it out for debugging purposes. Does Excel produce the warning when you call that code? After that note that Wordkbook.Close - not Wordkbooks.Close() - allows you to specify whether to save changes or not.

Michael Kaden writes:
The Instance is NOT removed from svchost even after Excel is completely closed

Svchost? Sorry? This isn't a program that we deal with when developing add-ins for Office on Windows Desktop. Do you try to wait for a couple of minutes?

If you start your WPF form from an add-in *and* if the add-in is an Excel COM add-in, you should get rid of Dim xlApp As New Excel.Application and use the ExcelApp property the add-in module provides.

If however you start your WPF from from a standalone application - not from an add-in - Add-in Express can't relate to this issue as Add-in Express is about COM add-ins.

Anyway, make sure you have no VBA macros or other COM and Excel add-ins involved.

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Thank you Andrei,

changing form Dim xlApp As New Excel.Application to

Dim wb As Excel.Workbook = Nothing
Dim wbs As Excel.Workbooks = AddinModule.CurrentInstance.ExcelApp.Workbooks

solved the problem. Thank you also to

https://www.add-in-express.com/creating-addins-blog/2013/09/23/excel-addin-development-application-base-objects/

It helped getting it all together

Thank you very much again & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18817
Joined: 2006-05-11

You are welcome!

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Hello Andrei,

1.) I am trying to understand why

Dim wbs As Excel.Workbooks = AddinModule.CurrentInstance.ExcelApp.Workbooks

works and

Dim xlApp As New Excel.Application

not? I read in ADX documentation that the ExcelApp Object shall be used otherwise some unpredictable results might happen,

Can you point me to some documentation to get a better understanding of why?

2.) a more easy one (only for you)

    Private Sub Button2_Click(sender As Object, e As System.Windows.RoutedEventArgs)
        Dim wb As Excel.Workbook = Nothing
        Dim wbs As Excel.Workbooks = AddinModule.CurrentInstance.ExcelApp.Workbooks
        Dim openfile As New OpenFileDialog
        openfile.Filter = "Microsoft Excel|*.xls*"
        openfile.ShowDialog()
        DataFile = openfile.FileName
        wb = wbs.Open(DataFile, 0, True)

'# reading data from Excel file

        wb.Close(SaveChanges:=False)
        Marshal.ReleaseComObject(wbs)
        Marshal.ReleaseComObject(wb)
    End Sub

I want to read data from wb workbook but I do not want the wb file to come up at all on the screen.
I cannot find a property "wb.visible = False" or similar
If I use
AddinModule.CurrentInstance.ExcelApp.Visible = False
Then all open Excel Instances get invisible (what I would expect)

I read that the default setting would be that wb visible = False
However, when i run the AddIn Button 2 then wb Excel Workbook is coming up on the screen-

Thank you & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18817
Joined: 2006-05-11

Hello Michael,

I don't think such a documentation exists. AddinModule.CurrentInstance.ExcelApp refers to the Excel.Application object that the add-in was supplied with. Getting a new Excel.Application may work or not depending on what you do with the new Excel.Application object and how this is supported in Excel. Say, in processes you only see a single EXCEL.EXE. That means, getting a new Excel.Application won't produce a new Excel process and thus you may not be able to set ExcelApp.Visible on it.

You can set ExcelApp.ScreenUpdating = true, process your workbook, close it and then set ExcelApp.ScreenUpdating = false. Failing to restore this property - say, in case of an exception - makes Excel unusable.

Also, Workbooks.Open provides the AddToMru parameter that you might want to set to false.

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Dear Andrei,

thank you for your information. I understand, and tried it out to use ExcelApp.Visible and found out that it is unpredictable which Window is hidden, the original Workbook, the WPF Window started by the original Workbook or the new Workbook. As you mentioned for ScreenUpdating, if there is an exception and because of the ExcelApp.Visible = true is not executed, I have to restart Excel to get it back to normal. Is it hat what you meant with "Unusable"?

What I want to achieve, is that the user is not confused by new Excel windows "popping up and disappearing" on his screen when we want to retrieve data from another Excel Workbook.

So I will try

ExcelApp.ScreenUpdating = false
process the workbook, close it and then set
ExcelApp.ScreenUpdating = true

Difficult however to guess how likely an exception can happen in that sequence.

If I understand the MS documentation right, the default for AddToMru is set False already. Or did you mean "True" so it is easier to get back to it should the above mentioned exception happen?

With regard to Microsoft and ADX Namespaces I have some problems in understanding, but will write a separate topic as it is a more general query.

Thank you for your support & kind regards

Michael