Properly Close Excel After Exporting Spreadsheet in VB.NET

Hello,

I am working on a Windows application using Visual Studio 2003. I am trying to export the results of a SQL stored procedure into an Excel spreadsheet.  I am able to do so.  My file does get created and the data is fine.

However, I see that Excel does not close by itself after my code runs.  If I run this procedure 5 times and then go to Task Manager, I see 5 copies of Excel running in the background. Apparently, Excel is not being properly closed.  If I exit my application, then all 5 instances of Excel close too.

Is there a way to force Excel to close after it finishes exporting?  

I added the following reference to my VB.Net Application =

Excel
      (name) Interop.Excel
      (description) Microsoft Excel 9.0 Object Library
_____________________________

Thanks!

This is the code I am using right now:
Dim ExlApp As Excel.Application
Dim iCol, iRow, iColVal As Integer
Dim bNew As Boolean
Dim i As Integer
FilePath = "c:\MyExcelFile.xls"
' Open the document that was chosen by the dialog
Dim aBook As Excel.Workbook
 
Try
    're-initialize excel app
    ExlApp = New Excel.Application
 
    If ExlApp Is Nothing Then
        ' throw an exception
        Throw (New Exception("Unable to Start Microsoft Excel"))
    Else
        'supresses overwrite warnings
        ExlApp.DisplayAlerts = False
        'check if file exists
        If File.Exists(FilePath) Then
            aBook = ExlApp.Workbooks.Open(FilePath)
        Else
            aBook = ExlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
        End If
        With ExlApp
            .SheetsInNewWorkbook = 1
            '.Workbooks.Add()
            .Worksheets(1).Select()
            'For displaying the column name in the the excel file.
            For iCol = 0 To ExportDataSet.Tables(0).Columns.Count - 1
                ''clear column name before setting a new value
                .Cells(1, iCol + 1).Value = ""
                .Cells(1, iCol + 1).Value = ExportDataSet.Tables(0).Columns(iCol).ColumnName.ToString
            Next
            'For displaying the column value row-by-row in the the excel file.
            For iRow = 0 To ExportDataSet.Tables(0).Rows.Count - 1
                For iColVal = 0 To ExportDataSet.Tables(0).Columns.Count - 1
.Cells(iRow + 2, iColVal + 1).Value = Trim(ExportDataSet.Tables(0).Rows(iRow).ItemArray(iColVal).ToString)
                Next
            Next
 
            'Check if the file exists
            If File.Exists(FilePath) Then
                ' It does. Save it 
                .ActiveWorkbook().Save()
            Else
                'Save it with the given file name
                .ActiveWorkbook().SaveAs(Filename:=FilePath)
            End If
            .ActiveWorkbook.Close()
        End With
       
 
        'Let the user know that the excel file was exported successfully
        MsgBox(FilePath & " File exported as Excel spreadsheet sucessfully")
 
 	  'clear the file path
	  FilePath = Nothing 
 
    End If
 
       
    'warn users if there's an error
Catch ex As Runtime.InteropServices.COMException
    MsgBox("ERROR: " & ex.Message)
Catch ex As Exception
    MsgBox("ERROR: " & ex.Message)
 
Finally
    ' Make sure that Excel is properly exited and al the COM objects are released
    ExlApp.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp)
    aBook = Nothing
    ExlApp = Nothing
End Try

Open in new window

TheUndeciderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jorge PaulinoIT Pro/DeveloperCommented:
You can try using Garbage Collector
 
 


Finally
    ' Make sure that Excel is properly exited and al the COM objects are released
    ExlApp.Quit()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    aBook = Nothing
    ExlApp = Nothing
End Try

Open in new window

0
TheUndeciderAuthor Commented:
Hello again,

I tried the garbage collector a moment ago and it cleared all the subsequent instances of Excel running except for the first one.  

In other words, I ran my code and I then I saw an instance of Excel on Task manager.  Once it the spreadsheet was exported and done, the Excel app was still  running in Task Manager.  If I run my code again, the second instance of Excel shows in Task Manager but it gets deleted right away.  If I do it again, it also gets deleted again, but the first instance is still there.

Any other ideas?
0
Jorge PaulinoIT Pro/DeveloperCommented:
I use that way and I have no problems.
Kill manually all instances in the Task Manager and then run it again. It should work fine.
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

TheUndeciderAuthor Commented:
Hello again,

I killed all instances of Excel manually, ran the code, and got 1 Excel process running in task manager again.  

If I run more Excel exports, the new Excel instances do dissapear as soon as they're done.  For some reason, that first instance of Excel doesn't wanna let go.  It's still there.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Try then to do
 aBook.Close()
0
TheUndeciderAuthor Commented:
If I do so, I get an error: The object invoked has disconnected from its clients.

I believe I am closing it already when I close the active workbook.
0
Jorge PaulinoIT Pro/DeveloperCommented:
You have to remove this ".ActiveWorkbook.Close()"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TheUndeciderAuthor Commented:
I just noticed something too.  The Excel process that stays in Task Manager after I ran the code again is not the first one, but the last one that ran.  The previous to that one is the one that gets deleted.
0
TheUndeciderAuthor Commented:
I removed the  ".ActiveWorkbook.Close()" and got the same results. Excel is still on Task Manager.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Can you show again you code ?
0
Dirk HaestProject managerCommented:
Every single Excel object that you created must be released and nulled out. This includes all Worksheets, Ranges, etc... Please see this MSDN article:
Office application does not quit after automation from Visual Studio .NET client
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q317109
private void ShutDownExcel() {
if (mExcelApp != null) {
mExcelApp.DisplayAlerts = true;
mExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComO bject(mExcelApp);
mExcelApp = null;
}
 
// Clean up memory so Excel can shut down.
GC.Collect();
GC.WaitForPendingFinalizers();
 
// The GC needs to be called twice in order to get the
// Finalizers called - the first time in, it simply makes
// a list of what is to be finalized, the second time in,
// it actually the finalizing. Only then will the
// object do its automatic ReleaseComObject.
GC.Collect();
GC.WaitForPendingFinalizers();
}

Open in new window

0
TheUndeciderAuthor Commented:
Sure here's my code again.

Also, I tried to do a test and see if I could just create an Excel file with nothing in it, save it, and then see if the Excel instance in Task Manager dissapeared.  

I have no idea why is failing on my real code.
Dim ExlApp As Excel.Application
Dim iCol, iRow, iColVal As Integer
Dim bNew As Boolean
Dim i As Integer
FilePath = "c:\MyExcelFile.xls"
' Open the document that was chosen by the dialog
Dim aBook As Excel.Workbook
 
Try
    're-initialize excel app
    ExlApp = New Excel.Application
 
    If ExlApp Is Nothing Then
        ' throw an exception
        Throw (New Exception("Unable to Start Microsoft Excel"))
    Else
        'supresses overwrite warnings
        ExlApp.DisplayAlerts = False
        'check if file exists
        If File.Exists(FilePath) Then
            aBook = ExlApp.Workbooks.Open(FilePath)
        Else
            aBook = ExlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
        End If
        With ExlApp
            .SheetsInNewWorkbook = 1
            '.Workbooks.Add()
            .Worksheets(1).Select()
            'For displaying the column name in the the excel file.
            For iCol = 0 To ExportDataSet.Tables(0).Columns.Count - 1
                ''clear column name before setting a new value
                .Cells(1, iCol + 1).Value = ""
                .Cells(1, iCol + 1).Value = ExportDataSet.Tables(0).Columns(iCol).ColumnName.ToString
            Next
            'For displaying the column value row-by-row in the the excel file.
            For iRow = 0 To ExportDataSet.Tables(0).Rows.Count - 1
                For iColVal = 0 To ExportDataSet.Tables(0).Columns.Count - 1
.Cells(iRow + 2, iColVal + 1).Value = Trim(ExportDataSet.Tables(0).Rows(iRow).ItemArray(iColVal).ToString)
                Next
            Next
 
            'Check if the file exists
            If File.Exists(FilePath) Then
                ' It does. Save it 
                .ActiveWorkbook().Save()
            Else
                'Save it with the given file name
                .ActiveWorkbook().SaveAs(Filename:=FilePath)
            End If
            
        End With
       
 
        'Let the user know that the excel file was exported successfully
        MsgBox(FilePath & " File exported as Excel spreadsheet sucessfully")
 
 	  'clear the file path
	  FilePath = Nothing 
 
    End If
 
       
    'warn users if there's an error
Catch ex As Runtime.InteropServices.COMException
    MsgBox("ERROR: " & ex.Message)
Catch ex As Exception
    MsgBox("ERROR: " & ex.Message)
 
Finally
    ' Make sure that Excel is properly exited and al the COM objects are released
    aBook.Close()
    ExlApp.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp)
    aBook = Nothing
    ExlApp = Nothing
 
    GC.Collect()
    GC.WaitForPendingFinalizers()
 
    GC.Collect()
    GC.WaitForPendingFinalizers()
 
End Try

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.