Solved

Excel Interop, VB.Net, process can't be destroyed

Posted on 2010-08-26
9
576 Views
Last Modified: 2012-06-27
In the attached code, in spite of the .Quit method being called, the Excel process does not end until I exit the application.

How can I force this process to close?
Public Shared Sub Export(ByVal f As Form1)

        If f.dgvRec.Rows.Count > 0 Then

            Try

                f.Cursor = Cursors.WaitCursor



                Dim xla As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()



                Dim wb As Microsoft.Office.Interop.Excel.Workbook = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)



                Dim ws As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(xla.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)



                ws.Cells(1, 1) = "Selected"

                ws.Cells(1, 2) = "PO"

                ws.Cells(1, 3) = "AG Item #"

                ws.Cells(1, 4) = "Description"

                ws.Cells(1, 5) = "Cus/Loc"

                ws.Cells(1, 6) = "Vend Item #"

                ws.Cells(1, 7) = "Qty Ordered"

                ws.Cells(1, 8) = "Qty Received"

                ws.Cells(1, 9) = "Qty Rejected"

                ws.Cells(1, 10) = "Reason Code"

                ws.Cells(1, 11) = "Qty Rem"

                ws.Cells(1, 12) = "Unit Cost"

                ws.Cells(1, 13) = "Rcv Value"

                ws.Cells(1, 14) = "Vend Qty Inv"

                ws.Cells(1, 15) = "Vend Unit Cost"

                ws.Cells(1, 16) = "Vend Value"

                ws.Cells(1, 17) = "Qty Var"

                ws.Cells(1, 18) = "Dollar Var"

                ws.Cells(1, 19) = "Off Inv"

                ws.Cells(1, 20) = "Receiver"

                ws.Cells(1, 21) = "Date"

                ws.Cells(1, 22) = "Voucher"



                For i As Integer = 0 To f.dgvRec.Rows.Count - 1

                    Dim dr As DataGridViewRow = f.dgvRec.Rows(i)



                    For j As Integer = 0 To 21

                        ws.Cells(i + 3, j + 1) = dr.Cells(j).Value.ToString()

                        Select Case j + 1

                            Case 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19

                        End Select

                    Next 'j

                Next 'i



                f.sfd.InitialDirectory = "c:\"

                f.sfd.Filter = "Excel files (*.xls)|*.xls|Excel files (xlsx.*)|*.xlsx"

                f.sfd.CreatePrompt = True

                f.sfd.OverwritePrompt = True

                f.sfd.Title = "Save Grid Worksheet"



                f.sfd.ShowDialog(f)



                If f.sfd.FileName.Trim.Length <> 0 Then

                    wb.SaveAs(f.sfd.FileName)

                End If



                wb.Close()

                xla.Quit()



            Catch ex As Exception

                Throw New Exception(ex.Message)

            Finally

                f.Cursor = Cursors.Default

            End Try





        End If



    End Sub

Open in new window

0
Comment
Question by:g_johnson
  • 4
  • 3
  • 2
9 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33539310
Every single Excel object that you created must be
released and nulled out. This includes all Worksheets, Ranges, etc... Please see this MSDN article:
http://support.microsoft.com/default...;EN-US;q317109


So you'll need this:

NAR(oSheet)
wb.Close()
NAR(wb)
xla.Quit()
NAR(xla)

Private Sub NAR(ByVal o As Object)
    Try
      System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
    Catch
    Finally
      o = Nothing
    End Try
  End Sub
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33539354
Dhaest --

Thanks.  I assumed that instead of oSheet I should use ws (my variable), and I did everything here.  It runs fine, without error, but the instance of Excel still exists until I close my application.  (The link, by the way, wouldn't load for me).  I can't spot another object that I need to null out, but maybe I'm missing something.

                Actions.NAR(ws)
                wb.Close()
                Actions.NAR(wb)
                xla.Quit()
                Actions.NAR(xla)
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33539546
Hi, I tried the following and it worked for me
        NAR(ws)

        wb.Close()

        NAR(wb)

        xla.Quit()

        NAR(xla)



        ws = Nothing

        wb = Nothing

        xla = Nothing

        GC.Collect()

Open in new window

0
 
LVL 5

Expert Comment

by:puffdaddy411
ID: 33541664
Since using ReleaseComObject or FinalReleaseComObject doesn't as well as performing a GC didn't always work for me, I began using the following method.  It works every time and does NOT affect other instances of Excel.
 
Here is the code as well as a link to another question I answered on this.  See the Author's comments for his results.
 
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26259278.html 

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 = &H12





Private Sub ExcelWorker



    MyExcel As Microsoft.Office.Interop.Excel.ApplicationClass

    

    'Blah Blah Blah Do all your Excel Stuffz



    MyExcel.Quit



    PostMessage(MyExcel.Hwnd, WM_QUIT, 0, 0)



    Dim i As Int32

    For i = 0 To 10000

        Application.DoEvents()

    Next



    MyExcel = Nothing



End Sub

Open in new window

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.

 
LVL 4

Author Comment

by:g_johnson
ID: 33542431
dhaest:  still won't die.  It's stubborn!

puffdaddy411:  I have option strict on.  What type is your Const?  I need to Dim Const WM_QUIT as SOMETHING = &H12.  Also, will the call work on any and all Windows versions?
0
 
LVL 5

Expert Comment

by:puffdaddy411
ID: 33542632
g_johnson,
 
"Dim Const WM_QUIT as Integer = &H12" will do the trick.  
 
I normally use this in WinXP.  However, I just tested this in a Windows 7 virtual box.  It seemed to work as advertised.  My Excel file exported and the process ended immediately.
 
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33542759
I'm sorry, but I'm doing something wrong.  In the PostMessage ExcelClass will not compile saying it's used before it has a value.  What am I doing wrong?

                Dim ExcelClass As Microsoft.Office.Interop.Excel.ApplicationClass

                Dim xla As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()

                Dim wb As Microsoft.Office.Interop.Excel.Workbook = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)

                Dim ws As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(xla.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

               blah, blah, blah
                Actions.NAR(ws)
                wb.Close()
                Actions.NAR(wb)
                xla.Quit()
                Actions.NAR(xla)

                ws = Nothing
                wb = Nothing
                xla = Nothing
                GC.Collect()

                PostMessage(ExcelClass.Hwnd, WM_QUIT, 0, 0)
0
 
LVL 5

Accepted Solution

by:
puffdaddy411 earned 500 total points
ID: 33542928
Well in your case, you shouldn't need "Dim ExcelClass As Microsoft.Office.Interop.Excel.ApplicationClass" since you are creating xla as New Excel.Application.  Also you won't need the ReleaseComObject code anymore (Actions.NAR()) or the GC.  Your Excel code is just a bit different from mine, but try this.  All you need to do is pass the " PostMessage" your Excel application object.
 
 
PostMessage(xla.Hwnd, WM_QUIT, 0, 0)



Dim xla As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()



                Dim wb As Microsoft.Office.Interop.Excel.Workbook = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)



                Dim ws As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(xla.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)



               'blah, blah, blah

                wb.Close()

                xla.Quit()



                ws = Nothing

                wb = Nothing



                PostMessage(xla.Hwnd, WM_QUIT, 0, 0)



                xla = Nothing

Open in new window

0
 
LVL 4

Author Closing Comment

by:g_johnson
ID: 33543546
That works!  Thank you very much.
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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

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

16 Experts available now in Live!

Get 1:1 Help Now