Excel Application.Quit Leaving Process Running 3

[blairacuda]

Hello Gurus,

i've got an app that uses excel as its reporting component. in our excel templates we have a macro to execute a bunch of different stuff. when users try to "reload" an older report (reload pushes new data into an old report) we check the macro version and if required, delete it then import the new version.

the following code gives us this ability (trimmed down sample):

Set XL = Excel.Application
Set WB = XL.Workbooks.Open(WORKBOOK_NAME, False)
Set VBP = WB.VBProject

With VBP
For i=1 to .VBComponents.Count
If .VBComponents(i).Name = WHATEVER
--execute version check and import stuff if necessary--
End If
Next i
End With

WB.Save
WB.Close
XL.Quit
Set XL = Nothing
Set WB = Nothing

For some reason the XL.Quit is not killing the process. I can still see Excel.EXE running in Task Manager after this code is executed; it only goes away once I close my program.

I can't seem to figure out what I am doing wrong.

Thanks for any help in advance.

CBlair
Crystal, InstallShield, branching out in other programming realms.

 

[HughLerwill]

>XL.Quit is not killing the process

Almost certainly because you have an unqualified reference to something in Excel. I don't think I can see one in the code you have posted so recheck the code you have not.
Recheck all keywords in the code which relate to Excel and be sure they all refer back to the XL object you Set at the top via dot prefixes.

 

[dilettante]

Looks like this one:

Set VBP = WB.VBProject

You also appear to be using early binding, a sucker's game with Office applications since they no not maintain binary compatibility between versions.

You don't need to cache references this way anyhow, you can almost always just write:

With CreateObject("Excel.Application")
    With .Workbooks.Open(WORKBOOK_NAME, False)
        With .VBProject
            For I = 1 To .VBComponents.Count
                If .VBComponents(I).Name = WHATEVER Then
                    --execute version check and import stuff if necessary--
                End If
            Next
        End With
    End With
    .Quit
End With

 

[TysonLPrice]

This link is a tutorial that includes ensuring you close properly and an explanation of early and late binding.

http://www.vbforums.com/showthread.php?t=391665

 

[blairacuda]

sorry the late reply on this.

i tried the suggestion dilletante gave and that is closing out the background process properly.

still need to read up a little on the article tyson provided, though. when it comes to late vs early binding i am still a student for sure.

i will be trying out some code today and will report what i find but at the moment it does look dilletante has provided the solution i need.

CBlair
Crystal, InstallShield, branching out in other programming realms.

 

[dilettante]

All I really showed was a way to avoid leaving dangling references to Excel objects though. You could set your danglers (e.g. VBP) to Nothing and it should work just as well.

 

[blairacuda]

I've been chasing this guy around for the better part of this week and haven't gotten anywhere. dilettante's code snippet was a good example and showed me where i was leaving some crumbs in my own code. even getting things screwed together as tightly i could and getting our lead developer looking at didn't seem to get rid of the lingering EXE.

aggravating and frustrating are the nicest words i've used on this issue this week. but i did find this code snippet on egghead cafe that cleans it up:

Public Sub KillProcess(byval processName as string)

On Error GoTo ErrHandler

Dim oWMI
Dim ret
Dim sService
Dim oWMIServices
Dim oWMIService
Dim oServices
Dim oService
Dim servicename

Set oWMI = GetObject("winmgmts:")
Set oServices = oWMI.InstancesOf("win32_process")

For Each oService In oServices

servicename = LCase(Trim(CStr(oService.Name) & ""))

If InStr(1, servicename, LCase(processName), vbTextCompare) > 0 Then
ret = oService.Terminate
End If

Next

Set oServices = Nothing
Set oWMI = Nothing

ErrHandler:
Err.Clear
End Sub

certainly wish we could figure out how to correctly close Excel without using the above code snippet but i'm at a loss and this works. sooo, i'm moving on.

but i am certainly open to any comments/suggestions/disgust you gurus have about that snippet.

thanks again everyone for your help with this so far.



CBlair
Crystal, InstallShield, branching out in other programming realms.

 

[HughLerwill]

>so recheck the code you have not

You don't seem to have posted the code in the '--execute version check and import stuff if necessary--' section yet.

 

[blairacuda]

here is the full code snippet causing me multiple headaches:

        MacroPath = G.getServerFile("Update\MacroUpdate.bas")
        UserModulePath = G.getServerFile("Update\User_Module.bas")
        If dir$(MacroPath) <> "" Then
            UpdateModule = ""
            UserModule = ""
            IniFile = G.getServerFileOrCopyDefault("GLOBAL\" & "MacroUpdate.ini")
            CurVer = ReadIni("MacroUpdate", "CurVersion", IniFile)
            If Trim$(CurVer) <> "" Then
                Set XL = Excel.Application
                Set WB = XL.Workbooks.Open(WrksheetPath, False)
                UpdateModule = ""
                UserModule = ""
                Set VBP = WB.VBProject
                If Err.Number <> 0 Then
                    MsgBox "Your Excel security settings do not allow the program to check that this report is updated with the latest macro's.  The report will still be run, but values cannot be guaranteed." _
                      & vbCrLf & vbCrLf & "To change your Excel security setting:" & vbCrLf _
                      & " 1. Open Excel manually." & vbCrLf _
                      & " 2. Select Tools - Macro - Security" & vbCrLf _
                      & " 3. Click the 'Trusted Sources' tab" & vbCrLf _
                      & " 4. Place a checkmark next to 'Trust access to Visual Basic Project.'" _
                      & vbCrLf & vbCrLf & "-Or- " _
                      & vbCrLf & vbCrLf & "To disallow QuoteExpress from checking if Reports need to be updated: " & vbCrLf _
                      & " 1. Open the Configuration screen." & vbCrLf _
                      & " 2. Select the Workstation Tab" & vbCrLf _
                      & " 4. Place a checkmark next to 'Disallow on the Fly Macro Updates' option" & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description, _
                      vbCritical
                Else
                
                    Set WS = WB.Worksheets("SortNames")
                    If WS.ProtectContents Then WS.Unprotect
                    WS.Cells(777, 1) = GServerDir & "\Data\"

                    With VBP
                        For I = 1 To .VBComponents.Count
                            If .VBComponents(I).Name = "QSIMacro" Then
                                UpdateModule = .VBComponents(I).Name
                                Exit For
                            End If
                        Next I
                        For I = 1 To .VBComponents.Count
                            If .VBComponents(I).Name = "User_Module" Then
                                UserModule = .VBComponents(I).Name
                                Exit For
                            End If
                        Next I
                    End With
                    If UpdateModule <> "" Then
                        If UserModule = "" Then
                            VBP.VBComponents.Import UserModulePath
                        End If
                        OldVer = Right$(WB.VBProject.VBComponents(UpdateModule).CodeModule.Lines(1, 1), 4)
                        If IsNumeric(OldVer) Then
                            If CInt(CurVer) > CInt(OldVer) Then
                                VBP.VBComponents.Remove VBP.VBComponents(UpdateModule)
                                VBP.VBComponents.Import MacroPath
                                If CInt(OldVer) < 460 Then 'If older than this version I need to add the ADO 2.8 Reference into the workbook
                                    AddExcelVBAReference VBP, "{2A75196C-D9EB-4129-B803-931327F72D5C}", 2, 8
                                End If
                            End If
                        End If
                        WB.Save
                        
                    End If
                End If
                WB.Close (False)
                If XL.Workbooks.Count = 0 Then XL.Quit
                Set XL = Nothing
                Set WB = Nothing
            End If
        End If

CBlair
Crystal, InstallShield, branching out in other programming realms.

 

[strongm]

Well, you've still got a dangling reference to VBP

 

[blairacuda]

wow... really wish i had remember to post that code earlier this weeek. my blinders i had on completely missed that. i was assuming that closing the WB would do that for me. threw a disconnect from WS for good measure (assumed the WB close was good enough for this too...).

took out the process murderer and added the following for the win:

Set VBP = Nothing
Set WS = Nothing

thanks a ton guys.

CBlair
Crystal, InstallShield, branching out in other programming realms.