Excel Still Running (1 Viewer)

[WayneRyan]

Hello All,

I have an Access 2007 app that instantiates an instance of Excel, opens a
spreadsheet and populates various cells with Access stuff.

So far, so good ...

But even if the user saves/exits Excel, the Excel instance keeps running.
Even if the Access VBA code does a quit, Excel keeps running.

Excel won't exit until the Access app finishes.

Access must be the parent app and for some reason it really has its hooks
into the Excel process.

I don't have any of the code here right now, but basically I just:

Dim xl As New Excel.Application
Dim wb As New Excel.Workbook
Dim ws As New Excel.Worksheet

Should I have just shelled Excel and then hooked to it?
I think it's fairly common as I've seen quite a bit on "KILLING EXCEL".

This is just bothersome at the point, but I'd really like to know what/why
is happening?

Thanks,
Wayne

 

[Banana]

We had to deal with similar problems, and lesson learn from this was that if the user wanted to view reports in Excel, it was preferable that we shell out to Excel rather than opening Excel.

Also, it's possibly to do with this:

Dim xl As New Excel.Application

New keyword can be problematic. Does it improve anything if we break it out to:

Dim xl As Excel.Application

Set xl = New Excel.Application

?

 

[ajetrumpet]

wayne,

remember that creating an excel instance in access is completely different than clicking on the icon to open it. both ways creates a running process in the windows log, so i would assume that windows still shows the instance running because it was not shut down by the same process from which it was opened. windows certainly isn't THAT smart.

from what i read, it sounds like you open it from acc, but the users close it by the control box or pressing alt+f4, etc...

if that is the case and is true, i would seriously bet that what i said above is true, but dont quote me 100 percent.

i bet you there is a log somewhere in windows that records the source of the caller of the instance, wheather it be the executable file, another program, or anything else that opened the program.

 

[boblarson]

Wayne:

I would need to see your whole code for using the Excel object, but my hunch is that you have something that is not tied to an instantiated object. Something like:

ActiveSheet

or

Selection

etc.

You need to tie those to an object:

xlWB.ActiveSheet

or

objXL.Selection

otherwise Access instantiates another instance of Excel which it then uses for those non explicitly referenced items. Then, it holds on to it until Access closes or you use Task Manager to end task. There is no way to close them otherwise because they aren't one of your instantiated objects, but are instead a system instantiated object.

 

[WayneRyan]

Banana,

Thanks, I tried with and without the New qualifier. It didn't make a difference.
I think that Shelling Excel would be wiser, I should be able to get the window
handle and control things from there.

Adam,

Exactly, Access VBA instantiates Excel, the user terminates Excel (or so they think).
Even if the VBA code terminates Excel, the spreadsheet will close, but Excel.exe
is still running.

I do have code to find Excel.exe and kill it, but I'd hate to be the one that
royally trashes someone's Excel session.

It's times like these that show how little we really know about some of the
tools we work with.

But ... If anyone can shed some light on the mechanics of this process I'd
appreciate it.

Thanks again,
Wayne

 

[WayneRyan]

Bob,

Now we're cooking!

I'll post the real code later (it's in the lab ... no internet).

I just have the xl (Excel object), wb (workbook) and ws (worksheet).

All references are made with respect to those things:

xl - just to open/close Excel
wb - just to get the worksheet name and worksheet itself
ws - just some references to ranges, rows and cells

xl.Quit seems to quit, but the Excel.exe process is still running.

I'll monitor with Task Manager when I get back.

I'll post a sample later.

Thanks,
Wayne

 

[Banana]

Bob:

Wow, didn't know that one. I have to admit that it's kind of scary you can call into those objects without the associated parent reference. It's kind like missing Option Explicit for objects.

 

[ajetrumpet]

pool players know all the angles

i AM a pool player, and honestly, i'm a SHARK, but dont tell ne one.

 

[boblarson]

Bob:

Wow, didn't know that one. I have to admit that it's kind of scary you can call into those objects without the associated parent reference. It's kind like missing Option Explicit for objects.

Yep, and all it takes is something as innocent as

Cells(1,2).Select

instead of

objXL.Cells(1,2).Select

as an example.

 

[WayneRyan]

Great to see the support here!

Watching the Task Manager showed me what was happening.

If your Access app has ANY reference to an Excel resource, Excel
won't close.

In my case it was a Range.

I have to:

Set rng = Nothing
Set ws = Nothing
wb.close
Set wb = Nothing
xl.close
Set xl = Nothing

Then ... Voila ... Excel closes

Access generally cleans up after itself, the impact isn't THAT significant if
you don't do something like --> Set rst = Nothing

The scope of DAO objects appear to be limited to the module, whereas
the scope of Excel objects appear to be tied to the Access app itself.

Thanks,
Wayne

 

[Banana]

Wayne,

Glad you sorted it out.

Call me a belt-and-suspender guy but I personally wouldn't like to refer to Excel objects without referencing the object variable. The project I mentioned earlier basically had a boatload of problems precisely because it was too easy to leave an Excel instance behind running.

In other words, if I were to write a project from ground up, I would prefer that I wrap all procedures that references Excel OM inside a With/End With:

With Xl
   .Cells(...)...
   ...
   .Range(...) ...
   ...
   .Worksheet(...)...
   ...
   .Save
   .Close
   .Quit
End With

Set Xl = Nothing

and when the user wants to be able to view the report, shell it out in a separate instance. This way we keep the formatting operations separate from viewing operations (which is also expected that user will dispose of the Excel instance at their discretion).

Mixing the process as well as not qualifying all Excel references is just going to be a big heartache.

But that's just my experience. I'm sure you will come up with something that works well for your needs.


Best of luck!

 

[WayneRyan]

Banana,

Are you sure that in your example the Excel.exe process will terminate?

I see what you're saying about referencing, but without explicitly setting
the range to Nothing it wouldn't stop!

Even though its parents (the worksheet, workbook and Excel itself) were all
closed (and set to Nothing), the range had to be totally and explicitly
eradicated.

Wayne

 

[boblarson]

Even though its parents (the worksheet, workbook and Excel itself) were all
closed (and set to Nothing), the range had to be totally and explicitly
eradicated.

How did you instantiate the range object? That is the important detail here. Banana is correct in what he says.

 

[WayneRyan]

Bob,

Drat! Notes are in the Lab.

I'm very sure that I use something like:

Set rng = ws.Range("A11:N1200")

But I won't know until I get back.

Are we saying that if you create stuff explicitly through their parents,
then when you dispose of the parents the kids are gone also?

Wayne

 

[boblarson]

I also mean how you declared it.

Did you use

Dim rng As Excel.Range

and then the set part. If it is declared that way and set the way you said, then it shouldn't be a problem. However, a

Dim rng As Range

would be a problem.

 

[boblarson]

Are we saying that if you create stuff explicitly through their parents,
then when you dispose of the parents the kids are gone also?

A disturbing analogy but essentially correct.

 

[WayneRyan]

Bob,

No, I definitely used --> Dim rng As Excel.Range

But until I explicitly set it to Nothing, Excel.exe persisted.

This little project was kinda fun, but I wanna go back to SQL Server.

As always ... Thanks (Bob and Banana)
Wayne

 

[Banana]

After testing, it seems that WayneRyan actually may be correct.

Private Sub doit()

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rng As Excel.Range

Set xl = New Excel.Application
Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)
Set rng = ws.Range("A1")

wb.Close (xlDoNotSaveChanges)
xl.Quit

Set ws = Nothing 'Excel not dead
Set wb = Nothing 'Excel not dead
Set xl = Nothing  'Still not dead
Set rng = Nothing 'Finally dies!

End Sub

While it is true for several other cases that child objects would be blown away when the parent object is de-referenced, this does not appear to be the case here, so in conclusion, it does appear it would be necessary to clean up every and each Excel object reference in order to destroy the Excel instance.

Fugly.

 

[boblarson]

What happens if you use

Set rng = xl.ActiveSheet.Range("A1:B5")

 

[Banana]

Bob, just changed my code to use ActiveSheet; same result.

In a way, I'm tempted to say, "Screw it, let's just blow off Excel with a call to kill process at end of the processing."