Excel Instance - Not closing 2

[Yazster]

Hi,

I have a situation here that is a bit confusing. I have an application, which exports data into an excel instance.

I've killed every instance using "SET X = NOTHING" for my instances of "EXCEL.APPLICATION", "EXCEL.WORKBOOK", and "EXCEL.WORKSHEET"

When I close my instance of Excel, and then my application, everything is fine. The same for when I close my application, then my instance of Excel.

Strange thing is, if I close the workbook, and not excel itself, the instance dissapears and remains in the TASK MANAGER, even after I've closed my application.

How do I remove the instance from the task manager, if the user quits the workbook and not excel? Is this normal?

Users of my application have requested that the instances of Excel remain open, even after closing the application that created them. This is easily done by not systematically quitting the excel application. Except, I'm worried that after my application is closed, I have no way of "cleaning" the remaining instances. Is this true?

Any help would be greatly appreciated.
Thanks,
Yazster

 

[SemperFiDownUnda]

before you set the application excel object to nothing execute its .Quit method.

ActiveX.EXE's do not always shut down when references to then are released.

Always use methods exposed to .Close/.Quit/.Exit etc before setting the object to nothing if the method is there.

 

[Yazster]

If I use the .QUIT method, my instance closes. I don't want this to happen.

 

[strongm]

The following is not a solution to your problem. However it may help you to understand what is causing the problem, and thus find an appropriate solution.

It is this simple: Set x = Nothing does not close or shutdown anything at all. All it does is decrement an internal object reference counter. Only when that counter equals 0 does the COM subsystem actually do any cleanup.

 

[RobS23]

I found that the order can have an effect. Release references from the bottom up e.g.Sheet, workbook, App, but you will have to quit the app first to release.

 

[irlbinky]

Dim oExcel As Excel.Application
Dim wb As Object
Dim ws As Object

With oExcel
Set wb = Workbooks.Open("path", , , , , , vbYes)
End With

Set ws = wb.Worksheets(1)

Enter Data

wb.Save

With wb
.Close
End With

Set ws = Nothing
Set wb = Nothing
Set oExcel = Nothing

It might be long but this is how I open and close an excel file and I only ever get a problem if someone else has the spreadsheet open when I write to it.

Hope it helps
Cathal

 

[SemperFiDownUnda]

What version of Excel are you using?
How are you creating a reference to it?

If you have the following code using Excel 8 Object Library then executing the .QUIT method of your instance should not shut down any open Excel Applications.

Dim x As Excel.Application
Set x = New Excel.Application
x.Quit
Set x = Nothing

What happens is when you do the
Set x = New Excel.Application
a new instances of the EXCEL.EXE will load. Excel is a Single Use Object meaning every new instance of Excel.Application will load another EXCEL.EXE into memory. If one of them crashes then you don't loose anything from any of the other instances.

your statement "If I use the .QUIT method, my instance closes. I don't want this to happen." confuses me. Either you want your application to close the excel instances it created or you don't. By default the instances are not visible but you can change that by the application.visible property.

If you create an instance of a Single Use COM object then you should destroy that instance.

Maybe I'm not understanding what you want to do.

 

[Yazster]

Hi everyone, thanks for all your help.

What I have is an application that connects to various databases, and displays the information on-screen. One of the features is the ability to dump the on-screen contents into an Excel Spreadsheet.

To do this, I use the the following:

Dim x as excel.application
Set x=createobject("excel.application&quot

I am able to export to excel no problem, I set the .visible property to TRUE so that the client may view the contents, and the main application remains open.

The client has requested two things:
1) They don't want the excel workbook to be saved, as they may not want to keep it. If they wish to keep it, they will save it themselves.
2) They want to be able to close the main application without closing the excel extract.

A few of you have mentioned to simply create the workbook, save it, and close the instance. This is not a problem, however as you can see, it is not what the client wants.

So basically, the instance must remain open even though the application that created it could possibly be closed. This is what is causing me problems.

Hope this clarifies things a bit.

Thanks again,
Yazster

 

[Schweiger]

One possibility would be to create a template for your workbook which contains the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Application.Workbooks.Count = 1 Then
        Application.Quit
    End If
End Sub

This code would ensure that your Excel-Instance would be closed after the last workbook is closed...
The code you have to put in the module of ThisWorkbook (I think that's right, but i don't know exactly the name because my excel is in german).
Now you have to add a new workbook based on this template:
oExcel.add TemplatePath

hope this helps
Andreas

 

[CajunCenturion]

This may very depend on how much interaction exists between the main application and Excel, but perhaps you could create and save the spreadsheet in a temp directory with the .visible property = False, Once the spreadsheet has been created, terminate the automated instance of Excel, then Shell out to Excel with the temp xls as a parameter.

This should cause a separate instance of Excel, unrelated to the main app with the created spreadsheet being open. The user can then terminate the main app, and has full control over excel as if it were started in native mode and they can choose if and when to save.

The main app would then have the additional burden of keeping the temp directory clean. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Yazster]

Thank-you for everyone's help!

CajunCenturion, I used your technique and it worked great.

Thanks again,
Yazster