Okay so my program opens excel, reads a few cells, and closes excel. But, it does not fully close excel and after multiple runs, I have multiple EXCEL.EXE *32 in my process list. It also asks for a save when it's closing when there are no changes being made, is there any way to avoid this? And how do you get Excel to fully close?
Right from the start what is wrong, how the automation is done from creating an instance of an Excel app to how you are assigning the variables for workbook and worksheet. Any time you can count more than one "." in a line of code working with Excel automation you are causing an object not to release (under normal conditions) when the code finishes executing commonly known as tunnelling.
The following example used as provided you should open Task Manager to the Process tab and notice that Excel appears while in use then disappears because the method used to close and dispose are done in a proper sequence and no tunnelling is done. Excel automation when done half way correct looks easy while done correctly requires a great deal of work to ensure every object is disposed of properly.
Requires an OpenDialog on the form this code is used. The default location is My Documents to find Excel files. Ignore the initial value for InitialPath variable.
Code:
Private Sub Button1_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
Dim InitialPath As String = "C:\DotnetLand2010\Excel\ExcelSoda\bin\Debug"
OpenFileDialog1.Title = "Please select a file to open"
OpenFileDialog1.FileName = ""
If IO.Directory.Exists(InitialPath) Then
OpenFileDialog1.InitialDirectory = InitialPath
Else
OpenFileDialog1.InitialDirectory =
System.Environment.GetFolderPath(
Environment.SpecialFolder.MyDocuments)
End If
OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls"
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName)
xlApp.Visible = False
xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
Dim SingleCellToRead = xlWorkSheet.Range("A1")
Dim A1_Value As String = String.Format("A1 = '{0}'", SingleCellToRead.Value)
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
If Not SingleCellToRead Is Nothing Then
Marshal.FinalReleaseComObject(SingleCellToRead)
SingleCellToRead = Nothing
End If
If Not xlWorkSheet Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
End If
If Not xlWorkBook Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBook)
xlWorkBook = Nothing
End If
If Not xlWorkBooks Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBooks)
xlWorkBooks = Nothing
End If
If Not xlApp Is Nothing Then
Marshal.FinalReleaseComObject(xlApp)
xlApp = Nothing
End If
MessageBox.Show(A1_Value)
End If
End Sub
Okay this is what I have now and it is working, I could not get the Marshal command to work still.
vb Code:
Dim strExcelPath As String = "C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls"
Dim xlApp = New Excel.Application()
Dim xlWbs = xlApp.Workbooks
Dim xlBook = xlWbs.Open(strExcelPath)
Dim xlSheet = xlBook.Worksheets(1)
Public Sub XLMain()
ReadXL()
ImportXL()
'Close Excel:
CloseExcel()
End Sub
Public Sub CloseExcel()
xlBook.Close()
xlApp.Quit()
KillProcess()
xlBook = Nothing
xlWbs = Nothing
xlApp = Nothing
End Sub
Public Sub KillProcess()
Dim proc = Process.GetProcessesByName("EXCEL.EXE")
For i As Integer = 0 To proc.Count - 1
proc(i).CloseMainWindow()
Next i
End Sub
Is this a good way to do this? It is working, but I'm not sure if this is the best way or if it will work every time. It does not close Excel until the Test Form is closed, this is going to be a Class Library, will it close when it is called in this case? The .DLL will be running on a windows service 24/7
Basically, Excel is still in the process list until the Form is closed now rather than when the KillProcess is called, if this is a class library instead of a form, will it do the same thing?
Last edited by thebuffalo; May 23rd, 2012 at 01:02 PM.
There is never a need to use your KillProcess code unless your code crashes before completion because of a run time exception. Under normal conditions what I have provided works. Get back to me about the Marshal.FinalReleaseComObject not working.
I had System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) and Marshal.ReleaseComObject(xlApp) in my CloseExcel but neither of them removed it from the process list, I never had a problem actually "closing" it but rather removing it as a process, but that being true it was never closed in the first place, it was just never visible so I wouldn't know.
See attached VS2010 project which uses the code I provided and also shows all open processes beginning with 'e' like Excel so you need not keep task manager open. Try CheckBox1 checked and unchecked (see comments inside)
I had System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) and Marshal.ReleaseComObject(xlApp) in my CloseExcel but neither of them removed it from the process list, I never had a problem actually "closing" it but rather removing it as a process, but that being true it was never closed in the first place, it was just never visible so I wouldn't know.
[RESOLVED] Closing Excel
Reply With Quote
Originally Posted by thebuffalo
