Ghost Excel Instance when starting Excel using VBA from within Access 2007

[crossh1]

When I create an instance of Excel using VBA within Access, then close Excel (using the quit method), it leaves the instance running (visible in the task manager). If I close Access, the instance disappears from the Task Manager.

Dim objXLApp As Excel.Application
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Quit
Set objXLApp = Nothing

How can I remove the instance of Excel while still in Access? Thanks for any help.

BTW I have searched the interenet high and low, have found this to be a common problem, but none of the responses worked for me.

 

[boblarson]

I take it you are not providing the full code because why would you open it just to close it. And I'm guessing that it is that code in between which is causing your problem.

You might want to read this before going further.

 

[crossh1]

I did not post the code in between because I ran it just like this and it caused the same problem. Therefore, your hyperlink does not resolve the issue. Also, I do tie the code to the specific application object I have instantiated.

What do you mean by "turning it off and on again"?

 

[boblarson]

I did not post the code in between because I ran it just like this and it caused the same problem. Therefore, your hyperlink does not resolve the issue.

Did you try running your code AFTER ensuring that there were no instances of EXCEL.EXE running in your Task Manager?

What do you mean by "turning it off and on again"?

[/quote]

That is a tag line in my signature which posts on every post. It is a line from a BBC comedy series - "The IT Crowd" and it is the first thing that they answer the phone with when someone calls about a computer problem.

 

[vbaInet]

Did you use this:

Dim objXLApp As [COLOR=Red]Object[/COLOR]

instead of this:

Dim objXLApp As Excel.Application

 

[boblarson]

Did you use this:

Dim objXLApp As [COLOR=red]Object[/COLOR]

instead of this:

Dim objXLApp As Excel.Application

I don't understand why the answer to that question would be relevant.

 

[crossh1]

Yes, I ran the code after checking the task manager to ensure Excel was not running. I also tried this Dim objXLApp As Object, no change. Excel still stays open until I close Access. Try it yourself and see.

 

[boblarson]

Yes, I ran the code after checking the task manager to ensure Excel was not running. I also tried this Dim objXLApp As Object, no change. Excel still stays open until I close Access. Try it yourself and see.

I just did and it works fine for me. It leaves nothing there. So there has to be something else causing it.

 

[vbaInet]

I don't understand why the answer to that question would be relevant.

Wanted to see if the results were different between both methods.

Just tested it and it doesn't leave the process running as well.

 

[crossh1]

You're kidding. You both ran this code from within an Access module, checked the task manager (before closing Access), and saw no instance of Excel running? I am using windows XP and Office Pro 2007. What versions are you both using?

 

[vbaInet]

Sure thing. This is how I tested it:

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
MsgBox "...closing..."
objXLApp.Quit
Set objXLApp = Nothing

With the Task Manager open, i can see the process when the msgbox pops-up. Once I click OK it disappears like magic

 

[boblarson]

You're kidding. You both ran this code from within an Access module, checked the task manager (before closing Access), and saw no instance of Excel running? I am using windows XP and Office Pro 2007. What versions are you both using?

No, I'm not kidding. And yes, I had no Excel processes running in the task manager before running it and after running it there were no Excel processes running it. I tried it using Windows XP SP3 and Office Pro 2007 SP2.

And I ran it EXACTLY as you had it (with no message box added).

 

[crossh1]

Thanks for your help. I'm not sure why I had a problem with just the open & close part (of course it's working ok now), but I changed a couple things in the code, and it appears to be closing properly now.
One thing I changed was:

Worksheets.Add().Name = rst![EmpName]

to:

objXLApp.Worksheets.Add().Name = rst![EmpName]

 

[boblarson]

One thing I changed was:

Worksheets.Add().Name = rst![EmpName]

to:

objXLApp.Worksheets.Add().Name = rst![EmpName]

And the original code would have caused a phantom Excel instance because it was not tied to an object that was either the application object or one that is a child of the application object.

Glad you got it working.

 

[Geek65]

Hi all. I am working in access VBA. I am dumping a report out to Excel and attempting to format the worksheet using Access VBA. I can get the code to adjust cell width and close/quit without leaving a ghost excel open in the task manager but as soon as I attempt to move a block of data it leaves the ghost excel running in memory/task manager. here is the two routines. First the one that works.

Private Sub CMDtest_Click()

Dim objXL As Object
Dim xlWB As Object
Dim xlSht As Object
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("C:\Apps\rptCSMPengineering2.xls")
'*********

Set xlSht = objXL.Sheets("TSSRSheet1")

With xlSht.Columns("A:A").ColumnWidth = 4.86
End With

'**************
MsgBox "done"

xlWB.Close True
objXL.Quit

End Sub
**************
Now the one that does not close.
Private Sub CMDtest_Click()

Dim objXL As Object
Dim xlWB As Object
Dim xlSht As Object
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("C:\Apps\rptCSMPengineering2.xls")
'*********
Set xlSht = objXL.Sheets("TSSRSheet1")
objXL.Sheets("TSSRSheet1").Range("K2:O9").Cut Destination:=Range("A2:E9")

'**************
MsgBox "done"

xlWB.Close True
objXL.Quit

End Sub

Any ideas what I am doing wrong? Oh I am running XP SP3 and Office Access 2007 SP2

 

[Mr. B]

In the code that does not work, try changing this line of code:

Set xlSht = objXL.Sheets("TSSRSheet1")
objXL.Sheets("TSSRSheet1").Range("K2:O9").Cut Destination:=Range("A2:E9")

to reference your "xlSht object variable:

Set xlSht = objXL.Sheets("TSSRSheet1")
xlSht.Range("K2:O9").Cut Destination:=Range("A2:E9")

See if that eleminates the instance of Excel being left behind.

 

[vbaInet]

Mr. B has just mentioned the most likely culprit. It would seem that the xlSht object creates an orphaned Excel instance. I would also include set those objects to Nothing:

Private Sub CMDtest_Click()

    Dim objXL As Object
    Dim xlWB As Object
    Dim xlSht As Object
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open("C:\Apps\rptCSMPengineering2. xls")
    '*********
    Set xlSht = objXL.Sheets("TSSRSheet1")
    xlSht.Range("K2:O9").Cut Destination:=Range("A2:E9")
    
    '**************
    MsgBox "done"
    
    xlWB.Close True
    objXL.Quit
    
[COLOR=Blue]    Set xlSht = Nothing
    Set xlWB = Nothing
    Set xlobj = Nothing[/COLOR]

End Sub

Some would argue this is not a necessity

 

[Geek65]

Found it.
changed ; objXL.Sheets("TSSRSheet1").Range("K2:O9").Cut Destination:=Range("A2:E9")

to; objXL.Sheets("TSSRSheet1").Range("K2:O9").Cut Destination:=objXL.Sheets("TSSRSheet1").Range("A2:E9")

She is all happy now.....

 

[vbaInet]

Ah... I didn't notice the Range bit

 

[Geek65]

Just as an FYI for others seeking to solve ghost instances of excel in the task manager after running Access VBA code make sure any use of or reference to Range, Column, Row or Font is always preceeded by your declaired object. Hope that helps someone. Gday all. Steve