Cannot kill Excel from taskmgr after Excel Export in VB 2014

sqdperu
sqdperu used Ask the Experts™
on
Using VB 2013 on Windows 7 64-bit and Office 365 (Excel) 32 bit.

I have some code that exports a DataGridView to Excel.  The code works fine.
I had an issue with MS Access so I ran a "Quick Repair" on Office 365 and it fixed the Access issue.

Now my Export to Excel still works fine.  However, the code the kills the Excel.exe*32 that likes to hang out in taskmgr now generates an error.

CODE:

    Declare Function PostMessage Lib "user32" Alias "PostMessageA" _
           (ByVal hwnd As Int32, ByVal wMsg As Int32, ByVal wParam As Int32, ByVal lParam As Int32) As Int32
    Const WM_QUIT As Object = &H12

 Dim xlApp As Excel.Application

... do the Excel export code successfully...

xlApp.Quit()

PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0)              <<<<  CODE THAT NOW GENERATES THE ERROR   <<<<<

xlApp = Nothing

--------------------------------------------------------------------

Code that I have tried in place of the error generating line of code (this code does absolutely nothing):

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
GC.Collect()
GC.WaitForPendingFinalizers()

-----------------------------------------------------------------------------

Error looks like this:
Error killing EXCEL.EXE*32
Any ideas why this one line of code no longer works after I did a "Quick Repair" on Office 365 and how do I fix it?

What would the code look like to trap this error and "Resume Next"?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
xlApp.Quit()

PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0)              <<<<  CODE THAT NOW GENERATES THE ERROR   <<<<<

xlApp = Nothing

why do you need that line in your code at the first place?

xlApp.Quit()
xlApp = Nothing

Open in new window


should be enough to close and clear an Excel application instance.

Author

Commented:
In a perfect world MS products would play nicely together and you would be correct - logical that would be the case.  In the real world, you are incorrect.   This is a well known problem and the solution I was using worked until after the "Quick Repair" on Office.

The Excel.exe will stay out there in the background and will not go away until you close your VB program.  It use to stack them, one for every time you did the export.  Now it no longer appears to stack them, but it will also not go away without that PostMessage running successfully.

taskmgr of stuck Excel.exe

Author

Commented:
Additional things I have tried to no avail:

Ran the "Online Repair" to Office 365.

-------------------------------------------------------------------------------

 xlWorkSheet = Nothing
 xlWorkBook = Nothing
 xlApp.Visible = True
 xlApp.SendKeys("%{F4}", True)
 xlApp.Quit()

------------------------------------------------------------------------------

           xlApp.Quit()

            Try
                If Not xlWorkSheet Is Nothing Then
                    Marshal.ReleaseComObject(xlWorkSheet)
                End If
                If Not xlWorkBook Is Nothing Then
                    Marshal.ReleaseComObject(xlWorkBook)
                End If
                If Not xlApp Is Nothing Then
                    Marshal.ReleaseComObject(xlApp)
                End If
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()
           Catch ex As Exception
                Exit Try
           End Try

        xlApp = Nothing

------------------------------------------------------------------------------

None of that will close the Excel.exe process.

And when I add the line "PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0)" it causes the error in Excel.exe.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Note that each time you call Excel object VB.NET creates a wrapper to communicate with Excell. Correct sequence should be
Dim xlApp As New Excel.Application
Dim wBooks = xlApp.WorkBooks ' Vital. Dim wBook =  xlApp.WorkBooks.Add() is incorrect since workbooks wrapper stay in memory
Dim wBook = wBooks.Add()
'...............
wBook.Close()
wBooks.Close()
xlApp.Quit()
Marshal.ReleaseComObject(wBook)
Marshal.ReleaseComObject(wBooks)
Marshal.ReleaseComObject(xlApp)

see MSDN for details

Author

Commented:
This solution worked great.  Searching the internet and I never found this.  I was coding it wrong as Ark suggested.  The MSDN link was very helpful.  Also, for anyone using this solution note,  Excel.exe may not instantly leave TaskMgr after this code runs.  I have had it stay out there 40 seconds to a minute.  So be aware, you might think the code is not working, but you just have to wait sometimes.

Excellent solution.  Thanks!
Glad I could help. Note that MSDN suggests looping ReleaseComObject untill it returns 0. You can use smth like:
Private Sub ReleaseExcelObject(o As Object)
    Dim counter =0 'To prevent endless loop if smth goes wrong
    Do While Marshal.ReleaseComObject(o) >0
        counter +=1
        If counter > 30 then exit do
    Loop
    o = Nothing
End Sub

Open in new window

wBook.Close()
wBooks.Close()
xlApp.Quit()
ReleaseExcelObject(wBook)
ReleaseExcelObject(wBooks)
ReleaseExcelObject(xlApp) 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial