VBA to run a second Excel session

[taigovinda]

Hi,

I am trying to automate a process, and the problem I have is that an add-in (SAP Bex 7) has a memory leak or something like that... I want to have Excel run a few Bex 7 queries and export the results. However, when I do this either manually or via code, it is fine for the first one, or maybe two or three, queries, and then bogs down quite a bit and/or fails. (Fine = less than 3 minutes, bogs down quit a bit = 45 minutes.) I am able to get around this by shutting out of Excel *completely* and then re-opening, then it performs like normal for the first query or two. I don't think I can do anything about the performance of the add-in, so I'd rather find a way to automate the work-around of opening a session, letting the code run, closing the session, opening another session, etc.

So my question: is there a way that I can automatically have Excel:

1. Open a particular book in a *new session*
2. Wait while all the code executes pursuant to that book's Open event
3. Close the new session completely
4. Loops steps 1 thru 3

Thanks for the help!

Tai

 

[taigovinda]

Hi Jaafar,

I ended up using your method in post #9, which works great except...

It turns out that SAP Bex 7 (the add-in that runs the queries) has a memory leak that won't be stopped just by quitting Excel. When running manually, the Excel process (in Task Manager) quits after you shut Excel. But if you run the add-in's macros to login to SAP Bex 7 and refresh your query, then shutting out of Excel will leave an Excel process (in Task Manager). I know which macro is causing it, but I need to run that macro to refresh my queries and I cannot access/alter the macro's code to try and "solve" the memory leak problem.

So, now I'm wondering if there is a good way (within Excel/VBA) to have the task manager kill all Excel processes, except the one that I want to stay alive.

I found some code on this site (http://www.vbaexpress.com/kb/getarticle.php?kb_id=811) and adapted it to the below, which will kill all but the oldest Excel process, based on the Creation Date. This should work, provided the Excel session that I don't want to close is always the oldest one. I know that one of the properties other than Creation Date is a "handle" but don't know if that is the right direction or how to get my session's handle.... Is there a more reliable way to make sure that I kill all Excel processes, except for the session that is doing the killing? Would it involve getting the "handle" for my Excel session and then killing the processes whose handles don't match? Thanks!

Tai

Sub TerminateExcelProcesses()
     '---------------------------------------------------------------------------------------
     '              : Terminates all Excel processes, except the oldest one.
     '              : Uses WMI (Windows Management Instrumentation) to query all running processes
     '              : then terminates ALL instances of Excel except the oldest one
     '              : "excel.exe" held in the variable strTerminateThis.
     '              :
     '              : ***WARNING: This will terminate a specified running process,use with caution!.
     '              : ***Terminating certain processes can effect the running of Windows and/or
     '              : ***running applications.
     '---------------------------------------------------------------------------------------
    Dim strTerminateThis As String 'The variable to hold the process to terminate
    Dim objWMIcimv2 As Object
    Dim objProcess As Object
    Dim objList As Object
    Dim intError As Integer
    Dim strMinDate As Variant
     
    strTerminateThis = "excel.exe" 'Process to terminate,
     'change notepad.exe to the process you want to terminate
     
    Set objWMIcimv2 = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2") 'Connect to CIMV2 Namespace
     
    Set objList = objWMIcimv2.ExecQuery _
    ("select * from win32_process where name='" & strTerminateThis & "'") 'Find the process to terminate
     
     
    If objList.Count = 0 Then 'If 0 then process isn't running
        MsgBox strTerminateThis & " is NOT running." & vbCr & vbCr _
        & "Exiting procedure.", vbCritical, "Unable to continue"
        Set objWMIcimv2 = Nothing
        Set objList = Nothing
        Set objProcess = Nothing
        Exit Sub
    Else
            For Each objProcess In objList
                If strMinDate = "" Or objProcess.CreationDate < strMinDate Then
                    strMinDate = objProcess.CreationDate
                End If
            Next
            For Each objProcess In objList
                If objProcess.CreationDate <> strMinDate Then
                    intError = objProcess.Terminate 'Terminates a process and all of its threads.
                    'Return value is 0 for success. Any other number is an error.
                    If intError <> 0 Then
                        MsgBox "ERROR: Unable to terminate that process.", vbCritical, "Aborting"
                        Exit Sub
                    End If
                End If
            Next
             'ALL instances of specified process (strTerminateThis) has been terminated
             
            Set objWMIcimv2 = Nothing
            Set objList = Nothing
            Set objProcess = Nothing
            Exit Sub
             
    End If
     
End Sub

 

[Jaafar Tribak]

.... Is there a more reliable way to make sure that I kill all Excel processes, except for the session that is doing the killing?

Something along these lines maybe :

Option Explicit

Private Declare Function GetCurrentProcessId Lib "kernel32.dll" () As Long


Sub test()
 
    If Not KillProcessByName("excel.exe", True) Then
    
        MsgBox "No other excel process found."
    
    End If
    
End Sub


Private Function KillProcessByName _
(ProcessName As String, Optional AllInstancesExceptCurrent As Boolean) As Boolean

    Dim objWMIService, objProcess, colProcess
    Dim strComputer, strList
    
    strComputer = "."
    
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" _
    & strComputer & "\root\cimv2")
    
    Set colProcess = objWMIService.ExecQuery _
    ("Select * from Win32_Process")
    
    For Each objProcess In colProcess
    If CBool(InStr(1, objProcess.Name, ProcessName, vbTextCompare)) Then
        If objProcess.ProcessID <> GetCurrentProcessId Then
            objProcess.Terminate
            KillProcessByName = True
            If Not AllInstancesExceptCurrent Then Exit Function
        End If
    End If
    Next

End Function

 

[taigovinda]

Perfect!! Thanks!!!