We help IT Professionals succeed at work.

Cannot kill Excel from taskmgr after Excel Export in VB 2014

202 Views
Last Modified: 2017-04-04
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

CERTIFIED EXPERT

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.
sqdperuApplication Development Engineer

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
sqdperuApplication Development Engineer

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.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
sqdperuApplication Development Engineer

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!
Ark
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions