[RESOLVED] Excel process not closing

[brucevde]

I have a simple procedure that opens an Excel workbook (Excel 10) and updates it. However even after Closing, Quiting and setting all objects to nothing the Excel process does not shut down - until the VB application is closed.

I was able to replicate the problem with this code which does nothing more than open the book and reference some objects.

Dim objExcel As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim objRange As Range
    
    Set objExcel = New Excel.Application
    Set objBook = objExcel.Workbooks.Open("C:\Testing.xls")
                
    Set objSheet = objBook.Worksheets(1)                
    Set objRange = objSheet.Range(Cells(1, 1), Cells(1, 2))
 
    Set objRange = Nothing
    Set objSheet = Nothing
    
    objBook.Close False
    Set objBook = Nothing
    
    objExcel.Quit
    Set objExcel = Nothing

The Excel process will shutdown if I comment out this line
Set objRange = objSheet.Range(Cells(1, 1), Cells(1, 2))

Something about that Range object is keeping Excel alive. Is there another method I should be using that will release the Range object? Setting it to nothing does not work.

[RobDog888]

Change your line where you dimension your range object.

'Form:
Dim objRange As Range
 
'To:
Dim objRange As Exce.Range

[brucevde]

How would that make a difference?

I did try it though but Excel still won't shut down.

[RobDog888]

Because when you reference just the default object without fully qualifying it with Excel. your creating a secondary reference to Excel. If you close one without explicitly closing the other then it will cause it to hang. So unless your coding in Excel VBA you should never use the default implied objects.

Did you make sure you started out with no hidden Excel.exe processes running?

Is there any macros in your testing.xls file?

[RobDog888]

I hear more about processes hanging with 2002 then anything else so far. The test code works fine for me under 2003. Have you done all the Office Updates and/or a Detect and Repair from the Help menu?

[RobDog888]

I just seen the reference to the default Cells objects. Those should be changed too.

Set objRange = objSheet.Range(Cells(1, 1), Cells(1, 2))
 
'to
Set objRange = objSheet.Range(objSheet.Cells(1, 1), objSheet.Cells(1, 2)) 'Or such

[brucevde]

Thanks anyway Rob.

I was able to get the Excel process to shutdown if I used this statement instead.

Set objRange = objSheet.Range(objSheet.Cells(1, 1), objSheet.Cells(1, 2))
 
'or this
 
With objSheet
   Set objRange = .Range(.Cells(1, 1), .Cells(1, 2))
End With

Not sure why it makes a difference using the Global.Cells method vs the Sheet.Cells method but problem solved.

[RobDog888]

I beat your post by a minute.

Like I said, its the implied reference that basically, for lack of a better explaination, duplicates a secondary Excel instance so to speak. Then by not destroying it it holds the object in memory causing Excel to remain even if your other fully referenced object vars are closed and destroyed.

[brucevde]

I beat your post by a minute.

Umm.. the phone rang...

I guess these "secondary Excel objects" would be inaccessible from the VB program

I tried this line as well but it did not work

Set objRange = objSheet.Range(objExcel.Cells(1, 1), objExcel.Cells(1, 2))

So bottom line never use any methods listed in the Object Browser under Globals.

[RobDog888]

Yes. But you need to access the .Cells from the sheet object and not the application opbject.

'No
objExcel.Cells(1, 1)

'Yes
objSheet.Cells(1, 1)

You need to multitask like I do then