Excel won't exit.

[GIS_Mike]

Hi everyone.
This is a big day, my first vbforums post!

I have a macro that calls Excel from another program, does some stuff, and exits Excel. My problem is that it doesn't really exit Excel. The Excel window will close but the program remains in Windows memory. This becomes a problem when I try to run the macro a second time.
If I go into the Task Manager and kill Excel, the macro generally works. Naturally, this isn't a viable solution for the end user.

A sample of my VBA code follows:

Dim MyXL As Object
Dim XLInputWorkbook As Object
Dim XLOutputWorkbook As Object

Later...

Set MyXL = CreateObject ("Excel.Application")
Set XLInputWorkbook = MyXL.Application.Workbooks.Open(FileName = "somepath")
Set XLOutputWorkbook = MyXL.Application.Workbooks.Add

I do my stuff and when I'm ready to close Excel do the following

MyXL.Application.Quit
Set MyXL = Nothing
Set XLInputWorkbook = Nothing
Set XLOutputWorkbook = Nothing

Should that not be enough?

I have also attempted to use a procedure to detect whether or not Excel is already running and use the current instance by calling

Set MyXL = GetObject("ExcelFilePath")

I am entirely new to programming so I don't doubt I'm making a huge error somewhere along the way. This problem has plagued me for months and I've checked and double checked to ensure I'm not forgetting to release variables or offer the correct sacrifices to the VB gods.

Thanks for the help

Mike

[Bartender]

Hey Mike.
I am also quite inexperienced to programming, but I may have an answer for you. You might have some references in your worksheet that directly call excel, which are preventing the program from closing. If part of your program uses excel without you meaning it to, it seems that it prevents excel from being able to close.
Using Option Explicit, if you don't might also help you avoid problems in this regard-- i think.
This guy explains it better than I do, probably because he actually knows what hes talking about where I don't--this is where I read everything I just told you:
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

Good Luck!

[Ecniv]

Did you close the workbooks before you finished with it?
Is there an error on the app.quit? Try App.Quit false

What bartender is on about is that a variable that holds a reference to your application, holds it open until it is cleared.

An example.
You make a form.
You instance the form (makes a copy of it in memory)
If you didn't hold the reference to the form anywhere, then it would close (almost immediately). However if you assign it to a global variable or collection, it is held open in memory (although the pointer you initially called it with can be set to nothing). So to close the form you'd then need to clear the reference in the global colleciton.


In your case, you have something that has not closed properly and is holding a reference to the excel object somewhere in your code.

You have to find where.

If you use On Error Resume Next, make sure you have a message box at the bottom of the sub to let you know if there is an error.

Breakpoint the code before you set the reference to the excel object to nothing. Use the immediates window to check whether it has closed properly.

[GIS_Mike]

Well, I looked into it and still don't have a solution. The only thing I can think is that I have a module that creates a generic Open File window the variables and procedures are all public. My guess is that unless I terminate all these values at the end of the program, something remains resident in memory and won't let Excel exit.
My big problem is that I borrowed the code for the Open File window from elsewhere and have no earthly clue how to implement something of my own.
Does anyone have some nifty code that I can use to make an open file window without those pesky public variables? Or is there a way to release all those variables (the procedure uses about 20) other than manually?

My suggestion is to hit the reset button in the VBA editor, but who wants to do that?

Thanks