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?
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
ASKER
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)
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)
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()
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.
https://www.experts-exchange.com/questions/26259278/How-to-cleanly-kill-an-Excel-object-from-memory.html
Here is the code as well as a link to another question I answered on this. See the Author's comments for his results.
https://www.experts-exchange.com/questions/26259278/How-to-cleanly-kill-an-Excel-object-from-memory.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
ASKER
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?
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?
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.
"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.
ASKER
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.E
Dim xla As Microsoft.Office.Interop.E
Dim wb As Microsoft.Office.Interop.E
Dim ws As Microsoft.Office.Interop.E
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.Hwn
Dim ExcelClass As Microsoft.Office.Interop.E
Dim xla As Microsoft.Office.Interop.E
Dim wb As Microsoft.Office.Interop.E
Dim ws As Microsoft.Office.Interop.E
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.Hwn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works! Thank you very much.
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.InteropServ
Catch
Finally
o = Nothing
End Try
End Sub