Excel Object not getting released

[KASH1]

Hi all,
I have crated a VB app that copies data from Access Database to an existing Excel Sheet. It does every fine except that
when I destroy the Excel Object "set Oxl = nothing", it never releases it from the memory. I could see the Excel Process running in the background even after the procedure is finished executing. I have looked several places and can't find anything.
Any help is appreciated.

Thanks

public sub ImportData()

Dim oXl As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oXl = CreateObject("Excel.Application")
Set oWB = oXl.Workbooks.Open(txtPath.Text)

For Each oSheet In Worksheets
oSheet.Activate
oSheet.Range("B17").Value = "Blah"
Loop

oWB.Save
oWB.Saved = True
oXl.Workbooks.Close
oXl.Quit

Set oWB = Nothing
Set oXl = Nothing

end sub

[McGenius]

Looks OK, however if your procedure was interrupted (never reached the end) for some reason only you may know (you were debugging, stepping through the code, etc) then object might never got released so it will hang in there until you shut down Windows. Perhaps, there is some other reasons.

[seaweed]

Same thing happens on my machine with your code. But the EXCEL process does stop once the VB App closes.

[Pirate]

Originally posted by McGenius
Looks OK, however if your procedure was interrupted (never reached the end) for some reason only you may know (you were debugging, stepping through the code, etc) then object might never got released so it will hang in there until you shut down Windows. Perhaps, there is some other reasons.

huh !

Try this

oXl.Workbooks.Close

[seaweed]

Uhhh, he is doing that!

[Pirate]

lol I can't read unformated code

[seaweed]

Actually, you may have been onto something, though. The problem was that oXl.Workbooks.Close is different than closing the Workbook object. So you need to do oWB.Close (after you save it) and then you can do oXl.Workbooks.Close

I put this code in (new stuff is in bold), and the Excel process stops:

Private Sub Command1_Click()
    Dim oXl As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    
    Set oXl = CreateObject("Excel.Application")
    Set oWB = oXl.Workbooks.Open("C:\Public\ExcelProject1\ExcelProject1.xls")
    
    For Each oSheet In oXl.Worksheets
        oSheet.Activate
        oSheet.Range("B17").Value = "Blah"
    Next
    
    oWB.Save 
    oWB.Saved = True    ' I dont think this is necessary, but I understand your intent
    [b]oWB.Close[/b]
    
    oXl.Workbooks.Close
    oXl.Quit
    
    Set oWB = Nothing
    Set oXl = Nothing
End Sub

[seaweed]

I just updated the code above. It's a simple one-liner addition!

[Pirate]

So the trick was in closing "oWB" obj ? That was my idea though

[seaweed]

hehe...that was almost your idea...you suggested oXl.Workbooks.Close, which he was already doing!

[KASH1]

I have tried oWB.Close. That still doen't work.
Plus I am making sure that I finish the subroutine. So the
the line Set oXL = Nothing gets executed.


Public Sub ImportData()

Dim oXl As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oXl = CreateObject("Excel.Application")
Set oWB = oXl.Workbooks.Open(txtPath.Text)

For Each oSheet In Worksheets
oSheet.Activate
oSheet.Range("B17").Value = "Blah"
Next

oWB.Save
oWB.Saved = True
oWB.Close
oXl.Workbooks.Close
oXl.Quit

Set oWB = Nothing
Set oXl = Nothing

End Sub

[Pirate]

I can't see anything wrong in his code ! . seaweed , Can you show me where the problem lies ?

[seaweed]

Try changing this:

For Each oSheet In Worksheets

to this:

For Each oSheet In oXl.Worksheets

That's the only difference I see.

[KASH1]

That fix the problem. Thanks a lot guys.