can't close Excel ! --- workaround found but now doesn't work any more !!!!

[phinds]

I have a funky problem w/ Excel. I open an existing XLS file, activate a sheet, work create some charts on the sheet, then close the app, BUT it doesn't close. Here's the code:

'  in a public module, I have:
    Public xlApp As excel.Application
 
    ' everything here down is in same module
    ' "open" code 
    ' prior to executing these statements, task mgr shows no Excel
    Set xlApp = New excel.Application
    xlApp.Workbooks.Open xlsfilename
    ' excel instance now shown by task mgr
 
    ' work with excel app here
 
    ' "close" code 
    xlApp.DisplayAlerts = False
    xlApp.ActiveWorkbook.Close SaveChanges:=True, filename:=xlsfilename
    xlApp.Quit
    Set xlApp = Nothing
    
    ' at this point, task manager shows an open instance of Excel
    ' but WHY ???

I have created some chart objects while working in the sheet, but I set them back to nothing (Set chart1 = Nothing) before leaving the function that manipulates them.

I can't see why Excel stays open. Once I close my VB app, the hidden instance of Excel goes away, but it SHOULD go away when I close it and quit it and set it to nothing, right ?

What else can I do to get rid of it (short of manual intervention via the task manager, which isn't an option for my computer-illiterate users).

Thanks for any help

[Blobby]

SHouldnt it be

Public xlapp as NEW excel.application

anyway?

[JoshT]

Originally posted by Blobby
SHouldnt it be

Public xlapp as NEW excel.application

anyway?

No, phinds's code is fine is this regard - the above will give you less control as to when Excel starts as you starting it instantly with the "New".

Anyway, for Excel, you need to call the "close" or "quit" or equivalent function for every object you use, as setting them to Nothing merely detaches the object reference from VB. You need to just look at all the objects you are using and make sure they all get closed.

[Frans C]

It is possible excel displays a hidden messagebox, that prevents it from closing.

Try to set DisplayAlerts to True, and Visible to True, just for testing purposes. This way you can find out if any messagebox pops up.

[techgnome]

'  in a public module, I have:
    Public xlApp As excel.Application
 
    ' everything here down is in same module
    ' "open" code 
    ' prior to executing these statements, task mgr shows no Excel
    Set xlApp = New excel.Application
    xlApp.Visible = True ' - This will show the Excel app, it may give some insight on what's going on...... I wonder if it's because it opens with a default "Book1.xls" ?????
    xlApp.Workbooks.Open xlsfilename
    ' excel instance now shown by task mgr
 
    ' work with excel app here
 
    ' "close" code 
    xlApp.DisplayAlerts = False
 
'Try looping through ALL workbooks, closing each one. Just a suggestion
 
    xlApp.ActiveWorkbook.Close SaveChanges:=True, filename:=xlsfilename
    xlApp.Quit
    Set xlApp = Nothing
    
    ' at this point, task manager shows an open instance of Excel
    ' but WHY ???

[phinds]

thanks, all, for the suggestions.

JoshT --- I already tried closing the chart objects I created but there is no close method for them so attempting to close them just gives an error message. I agree with you that this seems like the right thing to do, but it is not doable.

I'll try the other suggestions.

What I HAVE discovered since the first post is that if I do not create the chart objects, then excel closes just fine, so the problem pretty much HAS to be something to do with the creation of the chart objects, which is why I agree that JoshT's idea has merit except that I CAN'T close the chart objects, so I'm at a loss.

I tried "quit", "close" and so forth but there doesn't seem to be anything of that nature that you can do to a chart object.

[phinds]

OK, I've checked the other suggestions and still no joy.

Frans C, making it visible shows the excel application, but there are no hidden messages that aren't getting closed. Turning the display alerts back on just causes the "do you want to replace the exising file" message to come up (which is why I have the alerts off in the first place) but that happens whether the app is visible or not, so it isn't a hidden message.

techgnome, there's only the one workbook so it's the only thing available to close.

both good suggestions, but still no joy & I still haven't found anything that "closes" the chart objects that seem to be the cause of the problem.

[techgnome]

okay, now I know this sounds kinda loopy, but.... have you tried recording a macro while creating a chart, saving the worksheet and then closing Excel? I don't know if it will work or not... but..... ????
=-=-=-
The only other thing I can think of is to declare an object of Chart type, set the reference when creating the chart (use it to manipulate too of course) and setting it to Nothing when done saving. -- making the assumption that you are using something like app.workboox.sheet.chart to manipulate the chart.

Unfortunately, I got nothing else.

[Phenglai]

I would try something like this:

'  in a public module, I have:
    Public xlApp As excel.Application
    Public xlWorkbook as excel.workbook
 
    ' everything here down is in same module
    ' "open" code 
    ' prior to executing these statements, task mgr shows no Excel
    Set xlApp = New excel.Application
    set xlWorkbook = xlApp.workbooks.Open xlsfilename
    ' excel instance now shown by task mgr
 
    ' work with excel app here
 
    ' "close" code 
    xlApp.DisplayAlerts = False
    xlApp.ActiveWorkbook.Close SaveChanges:=True, filename:=xlsfilename
    xlApp.Quit
    Set xlApp = Nothing
    Set xlworkbook = Nothing

I added a new instance of the excel workbook and think this is where it might be getting hung in your code. I have had similar problems and changed my code to have an variable for each object in the excel spreadsheet.

Hope this helps

Jerel

[phinds]

again, thanks for the suggestions, but no joy

techgnome, I thought of the macro thing but the problem is that you can't capture the close process in a macro because the close process either closes the macro capture or isn't allowed 'cause the macro capture is on. As for your other suggestion, that's exactly what I'm doing. I've tried both "chart" and "object" in the declaration for my chart object, but it still doesn't work right.

Phenglai, your statement

set xlWorkbook = xlApp.workbooks.Open xlsfilename

just gives a compile error & I can't quite figure what you'r attempting here. Besides, the problem seems to be with the charts, not with the workbook. Remember, if I comment out all of the chart-related code, the problem goes away.

I've gone through the chart code again and had another programmer look at it and we can't find anything that gives a clue as to why the chart code should have this effect. All created objects are set to nothing (they can't be closed or quit because there's no such method for chart objects).


AAAARRRRRGGGGGGGG !!!!!

[WorkHorse]

I don't think the problem is in anything you have posted. All looks good. Even with Chart objects there should be no problem. If Excel quits--it quits. If the object is nothing--it is nothing. Either these lines aren't getting called or you're starting a new instance of Excel somewhere. You'll have to debug it. Step through the code and watch task manager to see how Excel apps are coming and going. If you've found a chunck of code you can take out that resolves the problem, try putting the lines back in small sections until the problem happens then see what those lines are doing that are causing the problem.

[phinds]

Yep, WorkHorse, I agree completely w/ everything you say and have already done what you suggest. That's how I found that the chart object is the culprit. At one point in the creation of the chart object, if I take out a line, most of the rest of the chart object stuff has to come out else I get a compile error, and when that line (and the rest) are commented out, the problem goes away. Only problem is that with those lines commented out, I don't get my chart.

This is really becoming serious. I have to ask my users to close my application and restart it to run the chart-creation code (which I now have invoked from a separate button). That works, but it's very user-unfriendly.

SO, folks, I'd still appreciate any help anyone can give. Here's the line that I have to take out in order to get excel to close (and the one following it):

Set myObj1 = xlApp.ActiveSheet.Range(cells(cd.dataRow, 4), cells(cd.dataRow, 39))
    chart1.SetSourceData source:=myObj1, PlotBy:=xlRows

and yes, I do set myObj1 and chart1 to nothing before trying to close excel (see earlier in this thread).

Here's my close sequence:

xlApp.DisplayAlerts = False
    xlApp.ActiveWorkbook.Close SaveChanges:=True, filename:=xlsfilename
    xlApp.DisplayAlerts = True
    xlApp.Quit
    Set xlApp = Nothing

[techgnome]

Geez phinds, what's the deal? It's STILL not working?

What would happen if you, say...
Create your excel file sans chart, closed everything. Then reopen it (this would all be transparent to the user), THEN add the chart and re-save it.
????

[phinds]

yeah, I tried that at one point, but I think I got sidetracked and didn't get back to it. It's an excellent suggestion and I need to look at it again. Thank you.

[phinds]

OK, now I remember what happened. If I close excel and then reopen it, it doesn't close but another instance opens. I know that sounds bizzarre, but it's what happens.

That is, If i leave out the chart code and just create the spreadsheet and then close it, all is well (and it closes). If I then close my application and run it again and invoke the chart-creation on the spreadsheet I just created, all is well and I have charts.

BUT ... if I attempt to have my code close the spreadsheet and then open it again to do the charts, the first instance doesn't close, another instance opens up and I get a message from the 2nd instance implying I'm trying to open and already-open file.

If I make the code create the spreadsheet, then close it, then wait until the user hits another button before reopening it and craeating the charts, it does create the charts, BUT I'm still faced with the fact that when I create the charts, excel won't close, so if the user attempts to use excel to examine the created spreadsheet, and he doesn't first close my application, excel hangs up in a very ugly way and has to be closed with the task manager.

This is ridiculous. I'm going nuts.

[techgnome]

Stupid MS crap...... is there a way you could open a trouble ticket w/ Microsoft Support?

[techgnome]

I found this on TechNet @ Microsoft....

You can also use the Application object's ActiveWorkbook property to return a reference to the currently active workbook. The Workbooks collection has a Count property you can use to determine how many visible and hidden workbooks are open. [B]By default, Excel typically has one hidden workbook named Personal.xls. The Personal.xls workbook is created by Excel as a place to store macros. If the hidden Personal.xls workbook is the only open workbook, the ActiveWorkbook property returns Nothing, but the Workbooks collection's Count property returns 1. The Workbooks collection's Count property will return 0 only when there are no hidden or visible open workbooks.[B}

I know that Excel stored "public" macros in this Personal.xls file...... hmmm.... I wonder.... ?????

[phinds]

when I go to close excel after creating the chart, there is a workbooks collection count of 1 and after I close excel there is a workbooks collection count of 0 BUT excel isn't closed.

I'm still going crazy.

What I'm going to do is boil this code down to a simple

open excel
create chart
close excel

& post all the code for all to see

thanks all for the attempts to help solve this nasty --- see you in the next post.

[NotLKH]

I have had a similar problem with Acrobat, but I've discovered that it doesn't happen if I don't have the app object dimmed publicly.

So, what would happen if you changed it so that you do this
locally inside your procedures:

Dim xlApp As excel.Application
Dim xlWorkbook as excel.workbook
....

and stay away from the persistant "Public" declaration.

Unless you need it to span multiple procedures/forms.

-lou

[phinds]

hm ... interesting idea; I'll give it a try. I DO have to span multiple modules, but I can use formal parameters and that might have the same effect since there will be no global declaration.

I've found a workaround which is to close the file under a different name. For some reason, that allows excel to close and then I can kill the file that just has the data and rename the file that has both the data and the charts.

[phinds]

no, it just occurred to me that won't work. I know because I boiled the whole problem down and posted it as:

http://www.vbforums.com/showthread.p...53#post1184053

and there are no public delcarations there, but it still doesn't work.

The work-around will just have to do, I guess.

[Sheppe]

Hi there,

I'm not clear on something in your post. I understand that you have a publicly declared excel application object, but from where are you calling the code that instantiates it (the code below your public declaration)? Is it in its own routine?

Anyhow, that aside, I am going to assume that you are calling the said code from its own routine. The first thing you should know is that every time you call that routine, you're going to create a new instance of excel. That could be part of the problem. Here's the other part - setting your object reference to nothing is not going to unload excel itself (though one would expect the "quit" statement above it to do that part), it is just going to destroy the object reference. Try putting in an "Unload xlApp" statement (without the quotes) above the line where you set it to nothing.

Lemme know how that works for ya.

[phinds]

I understand and agree w/ everything you said (and it's not a problem), except the "unload". It sounded like a good idea to me, but "Unload xlApp" (yes, without the quotes) just gives run-time error "can't load or unload this object"

ALSO, my workaround doesn't work any more. It worked once and now doesn't.

I'm seriously weirded out by all of this.

[techgnome]

I think I have an idea......

What about using the GetObject function to see if there is a current instance of Excel running..... if so, use it, if not, then use CreateObject to get it ... insead of the obj As New Excel.Application.

[Sheppe]

Originally posted by phinds
I understand and agree w/ everything you said (and it's not a problem), except the "unload". It sounded like a good idea to me, but "Unload xlApp" (yes, without the quotes) just gives run-time error "can't load or unload this object"

ALSO, my workaround doesn't work any more. It worked once and now doesn't.

I'm seriously weirded out by all of this.

In the past I had similiar issues with Excel not unloading. I ended up coding in a murderous routine, using the API, that would basically obliterate the application.

Here's a routine that isn't as forceful as the one I wrote, but it might work for you: http://216.26.168.92/api/tip2.html

[NotLKH]

I have seen how persistant that excell is. It refuses to die, until you unload your app. So,...

If it isn't too much of a headache, perhaps when you shutdown excell, you then start up a new instance of your app, pass it any info it needs to continue where your current app is leaving off,
then shutdown your current app, thus killing the persistant instance of excell.

But, this really is bothersome.

I'll play with it some more.

{I tried several different ways, and have even tried your code on
a second form. Even when the code finished, set the excell object
to nothing, unloading form2 and setting THAT to nothing,
its still active!}

-Lou