[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

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

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
g_johnson
Asked:
g_johnson
  • 4
  • 3
  • 2
1 Solution
 
DhaestCommented:
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
 
g_johnsonAuthor Commented:
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
 
DhaestCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
puffdaddy411Commented:
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
 
g_johnsonAuthor Commented:
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
 
puffdaddy411Commented:
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
 
g_johnsonAuthor Commented:
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
 
puffdaddy411Commented:
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
 
g_johnsonAuthor Commented:
That works!  Thank you very much.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now