Solved

Force to close Excel process thread

Posted on 2012-03-25
9
572 Views
Last Modified: 2012-03-26
Hi ,

I have this routine that force to closing an excel instance thread:


/////////

 Public Sub Excel_Chiudi(ByVal objExcel, ByVal objWorkBook, ByVal objWorksheet)

        Dim WMI
        Dim strQuery
        Dim objProcesses
        Dim objProcess
        Dim Handle_Excel As Long

        Try

     
1:          GetWindowThreadProcessId(objExcel.hwnd, Handle_Excel)


2:          objExcel.ActiveWorkbook.Close()
3:          objExcel.DisplayAlerts = True
4:          objExcel.Quit()
5:          objWorksheet = Nothing
6:          objWorkBook = Nothing
7:          objExcel = Nothing



8:          WMI = GetObject("winmgmts:")
9:          strQuery = "SELECT * FROM win32_process WHERE Name = 'EXCEL.EXE'"
10:         objProcesses = WMI.execquery(strQuery)

            For Each objProcess In objProcesses
11:             If objProcess.Handle = Handle_Excel Then
                    ' On Error Resume Next
12:                 objProcess.Terminate()
                End If
13:         Next

        Catch Ex As System.Exception

            Error_Handler(Ex)

        End Try


    End Sub

//////


This routine work fine but I need to remove the dipendence from the old Api instruction:

GetWindowThreadProcessId

In other word, I need to write only code that use .Net framework and not old win32 api like this.

Sameone can help me please to re-write this routine ?
0
Comment
Question by:luca345
  • 4
  • 4
9 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37763361
There doesn't seem to be a replacement for GetWindowThreadProcessId and I'd guess you'd otherwise have to use a lower level language to get at the processes.
0
 
LVL 40
ID: 37764034
Since we only have the code and no explanation of what you try to do or what the problem is, this is only a guess. Looks like you have problem with Excel instances being stuck running. Is that it?

Moving lines 5 and 6 before line 4 might do it, since you are releasing the worksheet and workbook before quitting. However, if there are other Excel objects referenced somehwere else, Quit might still do not do the job. All the objects referenced through a variable need to be released before Quit will work. You might have to deal with those in the calling methods.

Setting a variable to Nothing is usually sufficient in VBA, but since .NET and COM (Excel) are using different memory models, this  is not always sufficient when working Excel from .NET code.

The way to resolve that problem is to use the ReleaseComObject method. This is not as straightforward as simply terminating a process, but this is the way for .NET applications to release the memory and enable old COM application (such as Excel) to terminate when you are done with them and they do no want to react to Quit.

Look for ReleaseComObject in the documentation and in other posts. It will probablyl solve your problem.
0
 

Author Comment

by:luca345
ID: 37764573
Hi James,

The problem is a bug of microsoft:

When you do this:

         objExcel.ActiveWorkbook.Close()
3:          objExcel.DisplayAlerts = True
4:          objExcel.Quit()
5:          objWorksheet = Nothing
6:          objWorkBook = Nothing
7:          objExcel = Nothing

The Excel applicaion still active, and actually the only way that I known to close the excel instance is terminate the Thread process.

Do you known a way to solve this problem ?
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 37765922
This is not a bug from Microsoft. This is a side effect of manipulating a COM application (Excel) from a .NET application.

There are almost more differences between COM and Excel than there are between a PC and a MAC. Memory management is different between those 2 worlds. Microsoft did the best they could to make them work together, but there are limits to what you can do when you make a needed change toward a new system such as .NET.

If Microsoft is faulty of something, it is because they wait to long to get out a .NET version of Office.

Explore the ReleaseComObject command. There are examples of its use all over the Internet. It has been created to help you solve that kind of problem.

You way of declaring variables is also a very bad one and does not help. Variables should be decalred with a specific type. The way you do it, you let the system decide. The only variable were to take the care to define the type is with Handle_Excel As Long. All variables should be defined with a type.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Closing Comment

by:luca345
ID: 37766428
Excellent !
0
 

Author Comment

by:luca345
ID: 37766439
Hi James ,

I was try with  ReleaseComObject  and work fine !!

I need an excact clone in VB.net of the Old Format VB6 instruction.

If I open a new Thread are you interested ?
0
 
LVL 40
ID: 37766840
I suggest that you use the formatting functions in .NET. There are many of those that are adapted to different needs, so they are a lot more versatile. They are also more apt to work will in a multilingual environement if this is something that concerns your work.

String.Format is very close to the one that was used in VB6.
0
 

Author Comment

by:luca345
ID: 37766957
Hi James,

I need a function that is a clone of the old format because this function was called 50-100 times and I can't risk the insert only similar.

I was try to use the equivalent format method of vb.net but there is some difference and return something like ####,#### and not the value of the VB6 instruction.

Do you have the possibility to write a new function called S_Format that is a clone of the old VB6 format ?

If you can do it , I like to give you other 500 points in another post.
0
 
LVL 40
ID: 37768076
If I already had that function, I would give it to you, but I don't.

Sorry, but it would require too much time to write one and I do not have that time.

Good luck.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show, step by step, how to integrate R code into a R Sweave document
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now