can't close excel --- excellent analysis provided !

[phinds]

discussed in great detail in the thread at:

http://www.vbforums.com/showthread.p...hreadid=199211

I have reduced the problem down to a minimal code set given below. This code is the entire app. Put this in the load event of a blank form & just run the app. Here's what happens:

with task mgr, you'll see that there is no excel process prior to running the app, and when you run the app (which opens the form) task manager will show an excel process running. It SHOULDN'T be, since part of the code closes excel after having opened it. kill the app & excel closes, but it should have closed from the code at the end of the app.

If you comment out the chart creation code, then run the app, task manager will show you as excel opens and closes, but when you put the chart creation code it, excel opens, but never closes.

Option Explicit
'
' need to have, in the application path, an excel file called
' "book1.xls" and it has to have, in it's first worksheet
' (called "Sheet1") some numbers in the first 3 rows of the
' left-most column
'
Private Sub Form_Load()
    Dim ch As ChartObject
    Dim myObj1 As Object
    Dim xlApp As Excel.Application
    Dim xlsfilename As String
    
    xlsfilename = App.Path & "\book1.xls"
    Set xlApp = New Excel.Application
    xlApp.Workbooks.Open xlsfilename
    xlApp.Worksheets("Sheet1").Activate
    Set ch = xlApp.ActiveSheet.ChartObjects.Add(100, 20, 250, 170)
    ch.Chart.ChartType = xlPie
    Set myObj1 = xlApp.ActiveSheet.Range(cells(1, 1), cells(3, 1))
    ch.Chart.SetSourceData Source:=myObj1, PlotBy:=xlColumns
    If Err.Number <> 0 Then
        MsgBox "error"
    End If
    Set ch = Nothing
    Set myObj1 = Nothing
    xlApp.DisplayAlerts = False
    xlApp.ActiveWorkbook.Close SaveChanges:=True, FileName:=xlsfilename
    xlApp.DisplayAlerts = True
    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub
End Sub

so, can anyone figure out how to get excel to close after creating the charts? all help greatly appreciated

[kleinma]

what reference are you setting?

i just took your code and stripped the middle like you said

and excel loads as a process and then the process ends like it should... I set a reference to Excel 9.0

Private Sub Command1_Click()
'
' need to have, in the application path, an excel file called
' "book1.xls" and it has to have, in it's first worksheet
' (called "Sheet1") some numbers in the first 3 rows of the
' left-most column
'
    Dim ch As ChartObject
    Dim myObj1 As Object
    Dim xlApp As Excel.Application
    Dim xlsfilename As String
    
    xlsfilename = "c:\book1.xls"
    Set xlApp = New Excel.Application
    xlApp.Workbooks.Open xlsfilename
    xlApp.Worksheets("Sheet1").Activate
    xlApp.DisplayAlerts = False
    xlApp.ActiveWorkbook.Close SaveChanges:=True, FileName:=xlsfilename
    xlApp.DisplayAlerts = True
    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub
End Sub

[phinds]

Yep, that's what I would expect.

I use 10.0 but it shouldn't matter.

Without commenting out the chart creation, does excel close for you?

[JordanChris]

It does the same on my Windows 2000 / Office 2000 system.
With the chart bits, EXCEL opens and never closes until you stop the application.
Without the chart bits, Excel opens and closes as you would expect.

Sorry its not an answer, but at least it is consistent :-(

[phinds]

Thanks, JordanChris. I should mention that I'm using Office 2002 under Windows XP, so I'm glad to get confirmation that it happen in a different configuration because this sustains my belief that it is a generic Excel (or office automation) thing and that there IS a solution if we can just find it.

[JordanChris]

I have narrowed it down to these 2 lines:

Set myObj1 = xlApp.ActiveSheet.Range(Cells(1, 1), Cells(3, 1))
    ch.Chart.SetSourceData Source:=myObj1, PlotBy:=xlColumns

Comment these out, and Excel closes as expected.
Leave these in, and Excel only closes when the application shuts.

Maybe its not the chart. Maybe its set myObj1 to the active sheet? Maybe you can set it to something else later??

[phinds]

sorry, I should have mentioned that it is these two lines that are the culprit. They are what create the chart, so without them I have no chart.

[phinds]

bump

[phinds]

well, I found a work-around that solves the problem and isn't ALL that ugly, but still shouldn't be necessary.

What I do is, after I create the chart, I save the spreadsheet as a different file. Then when I close excel, it actually closes and I can kill the file that had just the data and rename the file that now has both the data and the chart.

It's still weird.

[WorkHorse]

phinds, you code works OK in my VB 5 with Excel 9. I tried it in VBA in Word and it raised some errors because variables aren't destroyed in VBA unless you END. Lets try cleaning up the code for the chart lines and see if that helps. First, declare myObj1 as an Excel.Range instead of Object because that is what the Source parameter expects. Set myObj1 to Nothing when you're done using it. Qualify the Cells property with your sheet so VB doesn't get confused. See if this helps.

Option Explicit
'
' need to have, in the application path, an excel file called
' "book1.xls" and it has to have, in it's first worksheet
' (called "Sheet1") some numbers in the first 3 rows of the
' left-most column
'
Private Sub Form_Load()
    
    Dim xlsfilename As String
    
    Dim xlApp As Excel.Application
    Dim ch As ChartObject
    Dim myObj1 As Excel.Range
    
    xlsfilename = "c:\book1.xls"
    Set xlApp = New Excel.Application
    'xlApp.Visible = True
    
    xlApp.Workbooks.Open xlsfilename
    xlApp.Worksheets("Sheet1").Activate
    Set ch = xlApp.ActiveSheet.ChartObjects.Add(100, 20, 250, 170)
    ch.Chart.ChartType = xlPie
    
    With xlApp.ActiveSheet
        Set myObj1 = .Range(.Cells(1, 1), .Cells(3, 1))
    End With
    ch.Chart.SetSourceData Source:=myObj1, PlotBy:=xlColumns
    
    If Err.Number <> 0 Then
        MsgBox "error"
    End If
    
    Set ch = Nothing
    Set myObj1 = Nothing
    
    xlApp.DisplayAlerts = False
    xlApp.ActiveWorkbook.Close SaveChanges:=True, filename:=xlsfilename
    xlApp.DisplayAlerts = True
    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub
 
End Sub

If that doesn't work, what happens if you make Excel visble and step throught the code? Is there just one Excel in the task bar and the Quit method doesn't do anything?

[JordanChris]

A great bit of analysis there, that code certainly works OK for me.

Excel is opened and closed at the correct time.

[phinds]

Well, WorkHorse, I certainly agree w/ JordanChris that that's an excellent bit of analysis.

I had used range object at one point but discarded it because it caused me some other grief, I forget what. The entire difference between your code and mine is that your way of setting myObj1 works and mine doesn't. Well, I mean, mine works for the chart but apparently doesn't get fully dumped when set to nothing (which, by the way, I did do in my code) whereas yours does.

I really thank you for taking the time to work this one out for me. It was driving me nuts. I must have spent half a work-day on it, which really wasn't justified, but I hate a mystery.

Thanks again.

[phinds]

by the way, I forgot to add, it doesn't matter whether you declare myObj1 as an object or as an excel range object. What matters is how you set it. My way of setting it must have left something hanging around after setting myObj1 to nothing, whereas your way didn't. So, in short, to reduce the difference down to the one line that matters, your line (well, effectively, your line) of

Set myObj1 = xlApp.ActiveSheet.Range(xlApp.ActiveSheet.Cells(1, 1), xlApp.ActiveSheet.Cells(3, 1))

works both to create the chart and to allow excel to close, but my line

Set myObj1 = xlApp.ActiveSheet.Range(Cells(1, 1), Cells(3, 1))

only works to create the chart but doesn't allow excel to close.

Any clue why? There must be some intermediate object that gets created and not destroyed, thus not allowing excel to close.

[phinds]

also, I should mention for anyone really interested in this whole issue, that my so-called workaround of saving the file with a different name proved to be ineffective. I don't know whether or not I was somehow fooling myself when I thought I got it working that way, but what I see now is that I cannot make it work reliably that way. WorkHorse's solution is clearly the way to go.

[kleinma]

phinds.. i assume this is working for you now then? via workhorses code?

well anyways.. here is an KB article that explains it...this talks about an error generated.. but it is the same issue.. not qualifying the cells property

http://support.microsoft.com/default...;en-us;Q178510

[phinds]

Matt,

Yes WorkHorse's code did the trick, AND I've finally figured out (I'm pretty sure) what's going on.

His way of doing it creates a reference to be used by the range-gathering process, whereas mine doesn't use a proper reference and therefore causes the creation of an unnmamed object that doesn't get removed, thus preventing excel from closing.

I'll check out the reference you provided.

Thanks.

[phinds]

OK, I checked out the reference that Matt provided, and it says exactly what I just said, which is that VB will create a reference in the middle of a line of code, if needed, even if the start of the line of code clearly references a known excel object and that furthermore, said reference can hang around and cause you grief, as it did for me.

What the article also points out is that while my statement about not needing to declare myObj1 as an excel object may be true some of the time (as it was in this case) it is dangerous programming practice and WorkHorse's suggestion of always using a proper reference to an excel object is definitely the way to go.

Once again, thanks to all who helped with this obscure and frustrating problem !