Solved

Force to close Excel process thread

Posted on 2012-03-25
9
571 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 45

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

757 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

21 Experts available now in Live!

Get 1:1 Help Now