Application won't die! (1 Viewer)

tkpstock · Apr 20, 2005

I am opening an Excel application from Access using VBA. When I execute the following code and then close the form, the Excel process is still running in Windows Task Manager - why? Is there some sort of garbage collection routine I need to run? Am I not killing the Excel application correctly? Here is my code:

Code:

    Dim oXL As Excel.Application
    Dim oWbk As Excel.Workbook
    Dim oWst As Excel.Worksheet
    Set oXL = CreateObject("Excel.Application")
    Set oWbk = oXL.Workbooks.Add
    Set oWst = oWbk.Sheets(1)
    oWst.Cells(1, 1) = "test"
    oWbk.SaveAs "c:\Temp.xls"
    oWbk.Close
    oXL.Quit
    Set oWst = Nothing
    Set oWbk = Nothing
    Set oXL = Nothing

Ultimately what happens is that I get a windows error "Null Pointer Problem" when I try to open up Excel again...

Thanks!!!

modest · Apr 20, 2005

It works for me. Only, I changed:
oWbk.SaveAs "c:\Temp.xls"
oWbk.Close

To

oWbk.Close True, "c:\Temp.xls"

tkpstock · Apr 20, 2005

Tried... nope. Dang!

I tried the change - didn't help. When I open the form, run the code, close the form / application, the EXCEL process is still there in task manager. As a matter of fact, if I run the code multiple times, multiple EXCEL processes are left open.

I don't suppose there is a way to kill a process, is there? Any further thoughts?

dt01pqt · Apr 20, 2005

You want to quit the application before you set to nothing. This is not the same as closing the worksheet. Seting to nothing doesn't kill the application. However you can't expect an application to quit emmediately it takes time.

tkpstock · Apr 20, 2005

If you read the code, you notice that I set to nothing at the end, so that isn't the problem. The application remains no matter how long I wait. If I run it multiple times, multiple instances of the application remain in Task Manager until I kill them manually.

Does Access have some sort of garbage collection routine? Does the windows API have something I can call?

modest · Apr 20, 2005

Put this in there before you save or close:

Code:

oXL.Application.ScreenUpdating = True

RoyVidar · Apr 20, 2005

Code looks fine to me.

Couple of things to try (if this is the only code!).

Try issuing DoEvents in the following positions:
- after the save
- after the quit
(- perhaps after the close?)

Perhaps release the worksheet and workbook prior to .quit?

You may also try going late bound (i e, remove the reference to Excel, and declare as objects in stead of excel.blah).

If neither of these works, I think I'd perhaps try a reinstall, but before that, try it on another PC, and see if you can replicate the behaviour.

tkpstock · Apr 20, 2005

Figured It Out!!!!

Believe it or not, the problem statement was:

Set oWbk = oXL.Workbooks.Add

Apparently when you create the XL object, it automatically opens a workbook, therefore I'm adding a second one - the first one is never destroyed, that's why the app never completely dies.

If I use

Set oWbk = oXL.Workbooks(1)

everything works great! Thanks for the help!

dt01pqt · Apr 21, 2005

Sure glad it worked. But quit should close everything or at least prompt you. Worksheets are a child of the application (they don’t appear on the task manager only EnumWindowProc is used.) so that implies that quit had been refused or is static. Sounds like a bug. Was visible true?

tkpstock · Apr 21, 2005

Actually, it is not working the same way today - I don't know why. Yesterday, when I created a new Excel.Applicaiton object, there was a workbook created automatically, which I could reference. Today, there isn't, so when I use the reference Set oWbk = oXL.Workbooks(1), I get a 'subscript out of range' error.

I am so &#@^*& confused...

tkpstock · Apr 21, 2005

Update...

Here's the latest code (visible is false by default):

Code:

Private Sub Command0_Click()
    Dim oXL As Excel.Application
    Dim oWb As Excel.Workbook
    Dim oWs As Excel.Worksheet
    Dim x As Integer
    Set oXL = New Excel.Application
    Set oWb = oXL.Workbooks.Add
    Set oWs = oWb.Sheets(1)
    oWs.Cells(1, 1) = "test"
    Set oWs = Nothing
    oXL.ScreenUpdating = True
    Kill "C:\Test.xls"
    oWb.SaveAs "C:\Test.xls"
    DoEvents
    oXL.DisplayAlerts = False
    For x = 1 To oXL.Workbooks.Count
        oXL.Workbooks(x).Saved = True
        oXL.Workbooks(x).Close False
    Next x
    Set oWb = Nothing
    oXL.Quit
    Set oXL = Nothing
End Sub

This still leave the Excel process running... dangit!!! What's wrong? If I run it 5 times, I get five open processes!!!

This may be a version-specific problem. Other persons have been able to paste this into an Access application and run it without the same problem.

The_Doc_Man · Apr 21, 2005

Probably you shouldn't use NEW to create the application object. Myself, I always use something resembling

set oXL = CreateObject( "Excel.Application" )

when I'm going to do something like that. If you don't do the oWB.close correctly before you declare "Set oWB = nothing" then the next click will create another NEW application and dereference the first instance of oXL. Leaving it dangling and forever uncloseable from Access. For that matter, why AREN'T you using oWB to trigger the closes rather than enumerating the open workbooks?

Probably you don't need the "oWB = nothing" if the next thing is a quit.

The ultimate arbiter is Excel, and apparently it doesn't think you have adequately closed all items that were opened.

tkpstock · Apr 21, 2005

The_Doc_Man said:
Probably you shouldn't use NEW to create the application object. Myself, I always use something resembling

set oXL = CreateObject( "Excel.Application" )

Did that in my first iteration. Didn't help.

The_Doc_Man said:
If you don't do the oWB.close correctly before you declare "Set oWB = nothing" then the next click will create another NEW application and dereference the first instance of oXL. Leaving it dangling and forever uncloseable from Access. For that matter, why AREN'T you using oWB to trigger the closes rather than enumerating the open workbooks?

Did that in my first iteration. Again, it did nothing to help. I am trying all variants of possibilities. Cut and paste the first code sample (first post) into an Excel application and tell me if it works for you. For that matter, try both examples. I enumerated the open workbooks in case there was more than one. That shouldn't make a difference. But who knows with Gatesisms?

The_Doc_Man said:
The ultimate arbiter is Excel, and apparently it doesn't think you have adequately closed all items that were opened.

Well, it ain't tellin' me a d*** thing, so I can only assume...

modest · Apr 22, 2005

The code you displayed worked for me. Running Windows XP Pro, Office 2003. It's been working for me too. What is the hardware specs on your computer?

Also try and compact and repair your database. This shouldn't have any effect, but you never know. If all else fails attach your database. I want to try and run it from the actual file that's causing problems to further determine if it's your machine or not.

dt01pqt · Apr 22, 2005

tkpstock said:
Did that in my first iteration. Didn't help.

Did that in my first iteration. Again, it did nothing to help. I am trying all variants of possibilities. Cut and paste the first code sample (first post) into an Excel application and tell me if it works for you. For that matter, try both examples. I enumerated the open workbooks in case there was more than one. That shouldn't make a difference. But who knows with Gatesisms?

Well, it ain't tellin' me a d*** thing, so I can only assume...

This clearly a bug. I would download the latest patches. If you have already done this then it is the patches that may be suspect. Also be very careful with your code using certain api calls improperly and possibly Active-X can not only corrupt the applications in question it can potentially corrupt the windows environment. So even if your code is right now, if you used bad code in the past this can cause problems. The only solution is to reinstall the programs affected. Also a third program could be interfering with the progress of excel like a macro virus. If you were to open up and close down excel normally does it still appear in the task manager afterwards?

tkpstock · Apr 22, 2005

dt01pqt said:
Also a third program could be interfering with the progress of excel like a macro virus.

This is a good possibility - we have several 'add-ins' and auto-loaded spreadsheets - it could be that one of those spawns something that won't die.

dt01pqt said:
If you were to open up and close down excel normally does it still appear in the task manager afterwards?

Nope, excel closes down fine. It should be noted that others within my organization experience the same problem using the same code. I'm now beginning to think that:

a) Our WinXP image (with MSO) contains some restrictions of some sort. Also, our enterprise roll-out team is noted for being a few months behind on patches, etc, because everything has to be 'certified' before installation.
b) Another third-party software (Excel add-in) may not be closing properly

This will be the last I can look at this today - I'm in a meeting offsite all day. Talk again on Monday!!!

modest · Apr 22, 2005

If it works for you sometimes and not others. This sounds more like a windows termination problem. While you would like to attribute this to some other "add-in" or program, if you're running the same applications and doing the same procedures, the problem is most likely not a foreign program.

I am still interested in what your system specs are. To me this just sounds like a case of win rot. If you can, try defragmenting your system, but also ensure you have the latest files as tkp suggested. Reinforcing what he said, this has been a noted issue and a lot of us have experienced it.

tkpstock · Apr 26, 2005

Update...

I am working in parallel on some excel VBA functions - I noticed in Excel that when I close a workbook using the wbk.Close method, the associated VBAProject DOESN'T close with it!!! This may be a source of the problem. Any more thoughts???

modest · Apr 26, 2005

switch to linux

tkpstock · Apr 26, 2005

Figured it out!!!! (I'm pretty sure)

I think I've finally figured it out. We use LiveLink here at work. It has an associated addin that wasn't allowing the associated VBA projects to close down properly - there was a similar problem with the Google desktop addins. Once I change the appropriate registry key, everything started working properly!

The registry key in question is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\Addins\LLAppInt.Connect

The value that was the problem:
LoadBehavior (3) <-- load on startup

Once I changed it to
LoadBehavior (9) <-- load on deman

everything started working properly. Yay!!!

Application won't die! (1 Viewer)

Cubicle Warrior

Registered User.

Cubicle Warrior

Certified

Cubicle Warrior

Registered User.

Registered User.

Cubicle Warrior

Certified

Cubicle Warrior

Cubicle Warrior

Immoderate Moderator

Cubicle Warrior

Registered User.

Certified

Cubicle Warrior

Registered User.

Cubicle Warrior

Registered User.

Cubicle Warrior

Similar threads

Users who are viewing this thread