CODEWITHSUNDEEP
vb.netexcel
Fraïssé
Fraïssé

Reputation: 166

Close Excel when user is finished

The task is really simple:

  1. I want to open an Excel document (.xls) from VB.net GUI
  2. The user will work on the Excel file
  3. The user closes the Excel file when it is finished
  4. I want the VB.net code to release the Excel object

The problem is that when the user closes the file, the Excel object still can be seen on the process tab of the task manager. If I were to write XlApp.quit() immediately after opening the file, the user won't get a chance to do anything. How can I tell when the user closes the Excel file so I could run the code to release the excel object?

What I have so far:

Dim xlapp as new Excel.Application
Dim xlwb as excel.workbook = xlapp.workbooks.open("file path")
xlapp.visible = true

'The user do work here'
'What should I put in between here to detect when the user exits the excel file???"

xlwb.close()
xlapp.quit()
releaseObject(xlwb)
releaseObject(xlApp)

Upvotes: 2

Views: 2255

Answers (2)

MK Wong
MK Wong

Reputation: 95

You can add event handler to detect the close event. See here https://support.microsoft.com/en-us/kb/822750

Upvotes: 0

Nybbe
Nybbe

Reputation: 404

I have had this problem not so long ago. I found my solution and adapted it to your question. Note that I have done this in VBA in Excel, so perhaps you need to make some adjustments. I think that the solution should work for you though:

Here is the code that lets the user edit the excel file:

Dim xlapp as new Excel.Application
Dim xlwb as excel.workbook = xlapp.workbooks.open("T:\tmp\Book1.xlsx")
xlapp.visible = true

'Loop until the Excel file is closed.
'I though I had to check if the user closed Excel and not just the excel-file,
'but it seems that Excel does not close even if the user does "Exit Excel".
Do 
    Sleep 1000  
    'Keep on looping as long as the Excel-file is part of the open workbooks.
Loop While objExist(xlApp.Workbooks,"Book1.xlsx")

'xlwb.Close    You can't run this command, as the workbook is already closed
xlapp.Quit
releaseObject (xlwb)
releaseObject (xlapp)

To be able to use Sleep in VBA, I need to declare it in the beginning of the module:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

This is my code for objExist:

Public Function ObjExist(objCollection As Variant, iname As String) As Boolean
'------------------------------------------------------------
'Purpose    Tell if a named element is part of a collection
'Expects    obCollection    A collection of objects (i.e Worksheets, Tables)
'           iname           The name of the object that we want to look for
'Returns    True if the named object exists in the collection, otherwise False.
'           Note that it returns FALSE also if any other error occurs, like
'           the objCollection not being a collection.
'------------------------------------------------------------
Dim a As Object
    On Error GoTo DoesNotExist
    Set a = objCollection(iname)
    ObjExist = True
    Set a = Nothing
    Exit Function

DoesNotExist:
    ObjExist = False

End Function

Good luck!

Upvotes: 1

Related Questions