Solved

Excel wont close - Still runs in task manager (vb.net)

Posted on 2010-08-25
9
650 Views
Last Modified: 2012-05-10
I Have the following code below which does not seem to kill the excel objects after they have been created.
I have tried the GC as well as the  System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference).

Thanks
Public Function NameOfExcelSheet(ByVal strFileToOpen as String) as String

        Dim arrSheet(0) as String

        Dim i as Integer = 0

     

        objExcel = CreateObject("EXCEL.APPLICATION")

        objWorkBook = objExcel.Workbooks.Open(strFileToOpen)

        

        For Each objSheet In objWorkBook.Worksheets

            'Me.ListBox1.Items.Add(tworksheet.Name)

            'msgbox(objSheet.Name)

            Array.Resize(arrSheet,i + 1)

            arrSheet(i) = objSheet.Name

            i = i + 1

        Next

        return arrSheet(0) ' Only need the first element

        

        objWorkBook.Close

        objExcel.quit ' Quit Excel

        '*************************************************** 

        'Release objects

        objWorkBook = Nothing

        objExcel = Nothing

        '*************************************************** 

        

     '   

     '   objWorkBook.Close

     '   objExcel.quit ' Quit Excel

     '   objExcel = Nothing

     '   objWorkBook = Nothing

     '   objSheet = Nothing

     '   GC.Collect()

     '   GC.WaitForPendingFinalizers()

     '   ReleaseComObject(objExcel)

     '   ReleaseComObject(objWorkBook)

     '   ReleaseComObject(objSheet)

    End Function

Open in new window

0
Comment
Question by:ross13
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:avarmaavarma
ID: 33524781
Try the following method to forcefully release the memory being held by the excel object (make a call to the FlushMemory where you are currently calling GC.Collect() )
Imports System
Imports System.Runtime.InteropServices

Public Class MemoryManagement

    <DllImport("kernel32.dll", EntryPoint:="SetProcessWorkingSetSize", ExactSpelling:=True, CharSet:=CharSet.Ansi)> _
    Private Shared Function SetProcessWorkingSetSize(ByVal process As IntPtr, ByVal minimumWorkingSetSize As Integer, ByVal maximumWorkingSetSize As Integer) As Integer
    End Function


    Public Shared Sub FlushMemory()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        If Environment.OSVersion.Platform = PlatformID.Win32NT Then
            SetProcessWorkingSetSize(System.Diagnostics.Process.GetCurrentProcess().Handle, -1, -1)
        End If
    End Sub
End Class

Open in new window

0
 

Author Comment

by:ross13
ID: 33525720
Hi,

Thanks for that. I added the code but the process still displays. I checked and the memory seems to keep going up for about another 15 seconds after the calls. Is it possible to get a pid for a created object and kill it?

Thanks
0
 
LVL 4

Expert Comment

by:avarmaavarma
ID: 33526421
Yes. Try the following code to kill the process - you need the name of the process in the 'GetProcessedByName' call as shown below
Dim runningAcroProcesses As Process() = Process.GetProcessesByName("YOUR_PROCESS_NAME_HERE")
Dim er2 As IEnumerator = runningAcroProcesses.GetEnumerator()

Imports System.ServiceProcess;

While er2.MoveNext()
	Dim currProcess As Process = DirectCast(er2.Current, Process)
	If currProcess IsNot Nothing Then
		currProcess.Kill()
	End If
End While

Open in new window

0
 
LVL 4

Expert Comment

by:avarmaavarma
ID: 33526643
I forgot to mention - you will need to import the following namespace to kill the process
IMPORTS System.ServiceProcess
0
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.

 

Author Comment

by:ross13
ID: 33526669
Thanks for the code snippit. It closes down excel as I specified “EXCEL.EXE” in the process name.
Is there a way of being able to uniquely identify the process that has been created by the objExcel = CreateObject("EXCEL.APPLICATION") via a PID / description etc.

cheers

Ross

0
 

Author Comment

by:ross13
ID: 33526677
The problem is that it closes down all excel documents so if the used has an open document then this could get terminated.

Thanks
0
 
LVL 4

Accepted Solution

by:
avarmaavarma earned 500 total points
ID: 33526793
The only way to identify a unique excel process would be to assign its window a name (a caption) - and then retrieve the handle to that window. The handle is one step away from the processId - which can then be used to kill the specific process.

You will need to set the caption for the application as follows
' set your application's window caption
App.Caption = System.Guid.NewGuid.ToString.ToUpper

Then you can call the EnsureProcessKilled function passing in the arguments (nothing, App.Caption) - e.g. EnsureProcessKilled(nothing, App.Caption)

Try that and see if it works.
' set your application's window caption

App.Caption = System.Guid.NewGuid.ToString.ToUpper



Declare Function EndTask Lib "user32.dll" (ByVal hWnd As IntPtr) As Integer

    Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _

           (ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr

    Declare Function GetWindowThreadProcessId Lib "user32.dll" _

           (ByVal hWnd As IntPtr, ByRef lpdwProcessId As Integer) As Integer

    Declare Function SetLastError Lib "kernel32.dll" (ByVal dwErrCode As Integer) As IntPtr



Public Sub EnsureProcessKilled(ByVal MainWindowHandle As IntPtr, ByVal Caption As String)

        SetLastError(0)

        ' for Excel versions <10, this won't be set yet

        If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _

      MainWindowHandle = FindWindow(Nothing, Caption)

        If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _

      Exit Sub ' at this point, presume the window has been closed.

        Dim iRes, iProcID As Integer

        iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)

        If iProcID = 0 Then ' can’t get Process ID

            If EndTask(MainWindowHandle) <> 0 Then Exit Sub ' success

            Throw New ApplicationException("Failed to close.")

        End If

        Dim proc As System.Diagnostics.Process

        proc = System.Diagnostics.Process.GetProcessById(ProcessID)

        proc.CloseMainWindow()

        proc.Refresh()

        If proc.HasExited Then Exit Sub

        proc.Kill()

    End Sub

Open in new window

0
 

Author Closing Comment

by:ross13
ID: 33537264
Got it to work. Thanks for you help.
0
 

Expert Comment

by:zachvaldez
ID: 37915467
How did you build the>> App. object

App.Caption = System.Guid.NewGuid.ToString.ToUpper
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

Suggested Solutions

Title # Comments Views Activity
only14 challenge 19 68
matchUp  challenge 9 93
groupNoAdj 7 85
word0 challenge 4 65
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
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…
This video teaches viewers about errors in exception handling.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

911 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