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

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

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

0
TheUndecider
Asked:
TheUndecider
  • 6
  • 5
1 Solution
 
jpaulinoCommented:
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
 
jpaulinoCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
jpaulinoCommented:
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
 
jpaulinoCommented:
You have to remove this ".ActiveWorkbook.Close()"
0
 
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
 
jpaulinoCommented:
Can you show again you code ?
0
 
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:
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now