Excel process not terminating

[timmeh_041]

Hey guys,

My program is fairly simple. Data is put into a worksheet, and a series of pivot tables and charts are created off it. For whatever reason, the Excel instance created is not killing itself. I have gone through pretty much every tutorial I can get my hands on and have no idea why this is not properly closing. Could a fresh pair of eyes have a quick look and help me out? Thanks.

Public Sub saveExcelFile(ByVal path As String)

      'excluded code to query database

        Dim app As excel.Application
        Dim wb As excel.Workbook
        Dim sheet As excel.Worksheet
        Dim range As excel.Range

        MsgBox("Creating Excel Instances")
        'start new excel application
        app = New excel.Application

        'set some settings
        app.DisplayAlerts = False

        'get new workbook
        wb = app.Workbooks.Add

        'get active sheet
        sheet = DirectCast(wb.ActiveSheet, excel.Worksheet)
        sheet.Name = "Data Extract"

        MsgBox("Finished Creating Instances")
        Dim objArray(ResultTable.Rows.Count + 1, ResultTable.Columns.Count)

        For i = 0 To ResultTable.Columns.Count - 1
            objArray(0, i) = ResultTable.Columns(i).ColumnName.ToString
        Next

        Dim iRow As Integer = 1
        Dim iCol As Integer = 0

        MsgBox("Copying Data")
        For i = 0 To ResultTable.Rows.Count - 1
            For j = 0 To ResultTable.Columns.Count - 1
                objArray(i + 1, j) = ResultTable.Rows(i).Item(j).ToString
            Next
        Next

        MsgBox("Finished Copying Data")
        MsgBox("Writing To Spreadsheet")
        range = sheet.UsedRange
        range = range.Resize(ResultTable.Rows.Count + 1, ResultTable.Columns.Count)

        range.Value = objArray
        range = sheet.UsedRange
        range.EntireColumn.AutoFit()
        MsgBox("Finished Writing To Spreadsheet")

        Dim PivotSheet As excel.Worksheet
        PivotSheet = wb.Sheets.Add()
        PivotSheet.Name = "Cluster Pivot"

        Dim destRange As excel.Range = PivotSheet.UsedRange
        Dim PT1 As excel.PivotTable


        PT1 = PivotSheet.PivotTableWizard(excel.XlPivotTableSourceType.xlDatabase, range, destRange)

        PT1.PivotFields("Cluster").orientation = excel.XlPivotFieldOrientation.xlRowField
        PT1.AddDataField(PT1.PivotFields("Status"), "Count of Status", excel.XlConsolidationFunction.xlCount)
        PT1.PivotFields("Status").orientation = excel.XlPivotFieldOrientation.xlColumnField
        PT1.PivotFields("Status").pivotitems("5. Not InScope").visible = False
        PT1.PivotFields("Cluster").pivotitems("Not Found").visible = False

        Dim ChartSheet As excel.Worksheet
        ChartSheet = wb.Sheets.Add
        ChartSheet.Name = "Cluster Chart"

        Dim chartpage As excel.Chart
        Dim xlcharts As excel.ChartObjects
        Dim mychart As excel.ChartObject
        Dim chartrange As excel.Range = PivotSheet.UsedRange

        xlcharts = ChartSheet.ChartObjects

        mychart = xlcharts.Add(10, 80, 1000, 500)

        chartpage = mychart.Chart
        chartpage.SetSourceData(chartrange)

        chartpage.ChartType = excel.XlChartType.xlBarStacked100
        chartpage.Legend.Position = excel.XlLegendPosition.xlLegendPositionBottom
        chartpage.ApplyDataLabels(excel.XlDataLabelsType.xlDataLabelsShowValue)
        chartpage.ChartArea.Font.Size = 15


        For i = 1 To chartpage.SeriesCollection.count
            Select Case i
                Case 1
                    chartpage.SeriesCollection(i).interior.color = RGB(127, 255, 0)
                Case 2
                    chartpage.SeriesCollection(i).interior.color = RGB(255, 255, 0)
                Case 3
                    chartpage.SeriesCollection(i).interior.color = RGB(255, 165, 0)
                Case 4
                    chartpage.SeriesCollection(i).interior.color = RGB(255, 0, 0)
                Case Else
                    chartpage.SeriesCollection(i).interior.color = RGB(255, 255, 255)
            End Select
            chartpage.SeriesCollection(i).border.color = RGB(0, 0, 0)
            chartpage.SeriesCollection(i).border.weight = excel.XlBorderWeight.xlThin
        Next


        wb.SaveAs(path)
        wb.Close()
        wb = Nothing
        app.Quit()
        MsgBox("Finished")



        releaseObject(chartrange)
        releaseObject(destRange)
        releaseObject(range)
        releaseObject(mychart)
        releaseObject(xlcharts)
        releaseObject(PT1)
        releaseObject(chartpage)
        releaseObject(ChartSheet)
        releaseObject(PivotSheet)
        releaseObject(sheet)
        releaseObject(wb)
        releaseObject(app)
  
        MsgBox("Finished Release")

end sub

 Public Sub releaseObject(ByVal obj As Object)
        Try
            While System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj) > 0
            End While
                obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()

        End Try
    End Sub

Thanks alot

[si_the_geek]

To start with you should remove this line:

        app.DisplayAlerts = False

That is a per-user setting which is likely to be permanently changed, which would cause annoyance for the user (such as closing a file they were working on without asking them if they want to save it first).

There might be situations where you need to set it for your code to work, but it should only be done for those specific parts that need it, and switched back as soon as possible afterwards.



As to the closing issue, try to ensure all child objects are tidied up before you start dealing with their parents. For example: release the range variable before the sheet, and then close and release wb, and finally close and release app.

[westconn1]

try
app = nothing
after
app.quit

[timmeh_041]

Thanks guys for your replies.

si_the_geek - I have removed the line of code which you have highlighted. I did not know it was per user, rather I though it was per instance. Big thanks for the heads up.

I have tired to re-arrange them but to no success. I then went back and commented out everything but the initial write to the excel file. At this point the Excel process terminated perfectly. I then added the code to generate the pivot table. At this point it went to hell again. I removed the destRange variable from the code completely, and ti still fails. So it has something to do with the placement/termination of the pivot table variable. Any more insight or help would be greatly appreciated.

westconn1 - In the function releaseObject, the = nothing is done after the releasecomobject function.

Below is my code after I commented out the chart section and removed the destrange variable.

    Public Sub saveExcelFile(ByVal path As String)
        Dim SQLRetrive As String = "SELECT Cluster, AccountName, Competency, Solution, Phase, TrafficLight, Status FROM [ReportData] WHERE TableID = 8 ORDER BY Cluster, AccountName"
        Dim Con As New SqlCeConnection(DataBaseCon)
        Dim Command As New SqlCeCommand(SQLRetrive, Con)


        If Con.State = ConnectionState.Closed Then Con.Open()

        Dim ResultData As SqlCeDataReader = Command.ExecuteReader
        Dim ResultTable As New DataTable

        ResultTable.Load(ResultData)
        Con.Close()
        MsgBox("Finished reading data")

        Dim app As excel.Application
        Dim wb As excel.Workbook
        Dim sheet As excel.Worksheet
        Dim range As excel.Range

        MsgBox("Creating Excel Instances")
        'start new excel application
        app = New excel.Application

        'get new workbook
        wb = app.Workbooks.Add

        'get active sheet
        sheet = wb.ActiveSheet
        sheet.Name = "Data Extract"

        MsgBox("Finished Creating Instances")
        Dim objArray(ResultTable.Rows.Count + 1, ResultTable.Columns.Count)

        For i = 0 To ResultTable.Columns.Count - 1
            objArray(0, i) = ResultTable.Columns(i).ColumnName.ToString
        Next

        Dim iRow As Integer = 1
        Dim iCol As Integer = 0

        MsgBox("Copying Data")
        For i = 0 To ResultTable.Rows.Count - 1
            For j = 0 To ResultTable.Columns.Count - 1
                objArray(i + 1, j) = ResultTable.Rows(i).Item(j).ToString
            Next
        Next

        MsgBox("Finished Copying Data")
        MsgBox("Writing To Spreadsheet")
        range = sheet.UsedRange
        range = range.Resize(ResultTable.Rows.Count + 1, ResultTable.Columns.Count)

        range.Value = objArray
        range = sheet.UsedRange
        range.EntireColumn.AutoFit()
        MsgBox("Finished Writing To Spreadsheet")

        Dim PivotSheet As excel.Worksheet
        PivotSheet = wb.Sheets.Add()
        PivotSheet.Name = "Cluster Pivot"

        Dim PT1 As excel.PivotTable

        PT1 = PivotSheet.PivotTableWizard(excel.XlPivotTableSourceType.xlDatabase, range, PivotSheet.UsedRange)

        PT1.PivotFields("Cluster").orientation = excel.XlPivotFieldOrientation.xlRowField
        PT1.AddDataField(PT1.PivotFields("Status"), "Count of Status", excel.XlConsolidationFunction.xlCount)
        PT1.PivotFields("Status").orientation = excel.XlPivotFieldOrientation.xlColumnField
        PT1.PivotFields("Status").pivotitems("5. Not InScope").visible = False
        PT1.PivotFields("Cluster").pivotitems("Not Found").visible = False

        wb.SaveAs(path)

        MsgBox("Finished")

        releaseObject(PT1)
        releaseObject(range)

        releaseObject(PivotSheet)
        releaseObject(sheet)

        wb.Close()
        releaseObject(wb)

        app.Quit()
        releaseObject(app)

        MsgBox("Finished Release")

    End Sub

    Public Sub releaseObject(ByVal obj As Object)
        Try
            While System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0
                MsgBox("Got In here")
            End While
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()

        End Try
    End Sub

Regardles of the order in which I use releaseObject, since releasecomobject is called while it is greater then 0 (ie. reference count is >= 1) should not all objects get nulled out anyway?

Any help you could provide would be greatly appreciated! Thanks!

[Siddharth Rout]

Try this...

Untested....

Dim oApp As Excel.Application, oBook As Excel.Workbook, oSheet As Excel.Worksheet
Dim oRange As Excel.range

MessageBox.Show("Done", "Creating Excel Instances", MessageBoxButtons.OK, _
MessageBoxIcon.Information)

oApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)

'oApp.Visible = True

oBook = oApp.Workbooks.Add
oSheet = DirectCast(oBook.Sheets(1), Excel.Worksheet)
oSheet.Name = "Data Extract"

MessageBox.Show("Done","Finished Creating Instances"), MessageBoxButtons.OK, _
MessageBoxIcon.Information)

'
'~~> Rest of the code
'

oRange = Nothing
oSheet = Nothing
oBook.Close(SaveChanges:=False) 'or obook.saveas (Path)
oBook = Nothing
oApp.Quit()
oApp = Nothing

[timmeh_041]

Thanks for the suggestion koolsid, but I have tired that and it doesn't work. Again, when I had it so it wasn't doing anything to do with the pivottable or chart it would close perfectly, but when I include just the pivot table, it fails to close. Any other thoughts would be greatly apperciated.

    Public Sub saveExcelFile(ByVal path As String)
        Dim SQLRetrive As String = "SELECT Cluster, AccountName, Competency, Solution, Phase, TrafficLight, Status FROM [ReportData] WHERE TableID = 8 ORDER BY Cluster, AccountName"
        Dim Con As New SqlCeConnection(DataBaseCon)
        Dim Command As New SqlCeCommand(SQLRetrive, Con)

        If Con.State = ConnectionState.Closed Then Con.Open()

        Dim ResultData As SqlCeDataReader = Command.ExecuteReader
        Dim ResultTable As New DataTable

        ResultTable.Load(ResultData)
        Con.Close()
        MsgBox("Finished reading data")

        Dim app As excel.Application
        Dim wb As excel.Workbook
        Dim sheet As excel.Worksheet
        Dim range As excel.Range

        MsgBox("Creating Excel Instances")
        'start new excel application
        app = DirectCast(CreateObject("Excel.Application"), excel.Application)

        'get new workbook
        wb = app.Workbooks.Add

        'get active sheet
        sheet = DirectCast(wb.Sheets(1), excel.Worksheet)

        sheet.Name = "Data Extract"

        MsgBox("Finished Creating Instances")
        Dim objArray(ResultTable.Rows.Count + 1, ResultTable.Columns.Count)

        For i = 0 To ResultTable.Columns.Count - 1
            objArray(0, i) = ResultTable.Columns(i).ColumnName.ToString
        Next

        Dim iRow As Integer = 1
        Dim iCol As Integer = 0

        MsgBox("Copying Data")
        For i = 0 To ResultTable.Rows.Count - 1
            For j = 0 To ResultTable.Columns.Count - 1
                objArray(i + 1, j) = ResultTable.Rows(i).Item(j).ToString
            Next
        Next

        MsgBox("Finished Copying Data")
        MsgBox("Writing To Spreadsheet")
        range = sheet.UsedRange
        range = range.Resize(ResultTable.Rows.Count + 1, ResultTable.Columns.Count)

        range.Value = objArray
        range = sheet.UsedRange
        range.EntireColumn.AutoFit()
        MsgBox("Finished Writing To Spreadsheet")

        Dim PivotSheet As excel.Worksheet
        PivotSheet = DirectCast(wb.Sheets(2), excel.Worksheet)
        PivotSheet.Name = "Cluster Pivot"

        Dim PT1 As excel.PivotTable

        PT1 = PivotSheet.PivotTableWizard(excel.XlPivotTableSourceType.xlDatabase, range, PivotSheet.UsedRange)

        PT1.PivotFields("Cluster").orientation = excel.XlPivotFieldOrientation.xlRowField
        PT1.AddDataField(PT1.PivotFields("Status"), "Count of Status", excel.XlConsolidationFunction.xlCount)
        PT1.PivotFields("Status").orientation = excel.XlPivotFieldOrientation.xlColumnField
        PT1.PivotFields("Status").pivotitems("5. Not InScope").visible = False
        PT1.PivotFields("Cluster").pivotitems("Not Found").visible = False




        MsgBox("Finished")
        wb.SaveAs(path)
        wb.Close()
        app.Workbooks.Close()
        app.Quit()

        releaseObject(PT1)
        releaseObject(PivotSheet)
        releaseObject(range)
        releaseObject(sheet)
        releaseObject(wb)
        releaseObject(app)
        MsgBox("Finished Release")

    End Sub

   Public Sub releaseObject(ByRef obj As Object)
        Try
            While System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0
                MsgBox("Got In here")
            End While
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

[westconn1]

i doubt that it is causing your issue, but it would be good practice to avoid using reserved words as variables
like
dim range as (excel.)range
makes it easier to read code too

[timmeh_041]

Well I gave up. I have spent to much time trying to track this issue down. Instead, I have released all explicitly declared variables and used the process described at

http://www.xtremevbtalk.com/showthread.php?p=1326018#post1326018

to kill the running excel process.

If anyone has any thoughts on it please let me know.