Copying Datagridview to excel - leaves excel process open

Question

Can anyone point out what is causing the excel process to remain open after my code finishes?

I've researched everything on this topic and the main point seems to be to avoid 3 dots with excel interop, but I believe I have done that and I still can't see where I'm going wrong.

my process is to copy the datagridview to the clipboard and then paste it in a newly created excel file, save it, close excel, and then give the user an option to open the file.

Two scenarios. First, when I run through the code and DON'T open the excel file it leaves the process open. Second, if I open the workbook and then close the workbook it doesn't leave a process running. I don't understand the difference because the opening of the workbook happens after I try to close and release all references to excel.

    Private Sub CopyDGVtoClipBoard()
    Me.DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
    Me.DataGridView1.RowHeadersVisible = False
    Me.DataGridView1.SelectAll()
    Dim dataObj As DataObject = Me.DataGridView1.GetClipboardContent
    If dataObj IsNot Nothing Then
        Clipboard.SetDataObject(dataObj)
    End If
    Me.DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithAutoHeaderText
    Me.DataGridView1.RowHeadersVisible = True
    Me.DataGridView1.ClearSelection()
End Sub

Private Sub ExportToExcel()
    If Me.DataGridView1.Rows.Count < 1 Then Exit Sub
    CopyDGVtoClipBoard()             

    SaveFileDialog1.Filter = "Excel File|*.xlsx"
    SaveFileDialog1.Title = "Save In"
    SaveFileDialog1.FileName = "Generic name"

    If SaveFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            If SaveFileDialog1.FileName <> "" Then
            Dim xlExcel As excel.Application
            Dim xlWorkBooks As excel.Workbooks
            Dim xlWorkBook As excel.Workbook
            Dim xlWorkSheet As excel.Worksheet
            Dim CR As excel.Range
            Dim misValue As Object = System.Reflection.Missing.Value

            xlExcel = New excel.Application
            xlExcel.Visible = True
            xlWorkBooks = xlExcel.Workbooks
            xlWorkBook = xlWorkBooks.Add(misValue)
            xlWorkBook.Application.DisplayAlerts = False
            xlWorkSheet = xlWorkBook.ActiveSheet
            CR = xlWorkSheet.Cells(1, 1)
            CR.Select()
            xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, True)
            xlWorkBook.SaveAs(SaveFileDialog1.FileName())
            xlWorkBook.Close(False)

            misValue = Nothing
            ReleaseExcel(CR)
            ReleaseExcel(xlWorkSheet)
            ReleaseExcel(xlWorkBook)
            ReleaseExcel(xlWorkBooks)
            xlExcel.Quit()
            ReleaseExcel(xlExcel)

            Dim OpenCheck As MsgBoxResult = MsgBox("Would you like to open the file?", MsgBoxStyle.YesNo, "Open File")
            Dim fPath As String = SaveFileDialog1.FileName
            If OpenCheck = MsgBoxResult.Yes Then
                Process.Start(fPath)
            End If
        End If
   End If

    Private Sub ReleaseExcel(ByVal O As Object)
    Do While System.Runtime.InteropServices.Marshal.ReleaseComObject(O) >= 0
        System.Runtime.InteropServices.Marshal.ReleaseComObject(O)
    Loop
    O = Nothing
End Sub

I've narrowed the issue down to the below code. If I do everything except specifying a range and adding data it'll close and end the process but as soon as I reference CR = xlWorkSheet.Cells(1,1) it hangs a process

            CR = xlWorkSheet.Cells(1, 1)
        CR.Select()
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, True)

Edit So I compared my code vs. what Karen posted and made some slight adjustments to my code (full code plus changes listed below), but for some reason it works and doesn't leave a process open! Not sure how, but I'll take it.

Private Sub CopyDGVtoClipBoard()
    Me.DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
    Me.DataGridView1.RowHeadersVisible = False
    Me.DataGridView1.SelectAll()
    Dim dataObj As DataObject = Me.DataGridView1.GetClipboardContent
    If dataObj IsNot Nothing Then
        Clipboard.SetDataObject(dataObj)
    End If
    Me.DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithAutoHeaderText
    Me.DataGridView1.RowHeadersVisible = True
    Me.DataGridView1.ClearSelection()
End Sub

Private Sub ExportToExcel()
    If Me.DataGridView1.Rows.Count < 1 Then Exit Sub
    CopyDGVtoClipBoard()

    Dim DateCon As DateTime = DateTime.ParseExact(Today.ToShortDateString, "M/d/yyyy", Nothing)


    SaveFileDialog1.Filter = "Excel File|*.xlsx"
    SaveFileDialog1.Title = "Save In"
    SaveFileDialog1.FileName = "Generic Name"

    If SaveFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
        If SaveFileDialog1.FileName <> "" Then
            Dim xlExcel As excel.Application = Nothing
            Dim xlWorkBooks As excel.Workbooks = Nothing
            Dim xlWorkBook As excel.Workbook = Nothing
            Dim xlWorkSheet As excel.Worksheet = Nothing
            Dim CR As excel.Range = Nothing
            Dim misValue As Object = System.Reflection.Missing.Value

            xlExcel = New excel.Application
            xlExcel.Visible = False
            xlExcel.DisplayAlerts = False

            xlWorkBooks = xlExcel.Workbooks
            xlWorkBook = xlWorkBooks.Add(misValue)

            xlWorkSheet = xlWorkBook.ActiveSheet
            CR = xlWorkSheet.Range("A1")
            CR.PasteSpecial(excel.XlPasteType.xlPasteAll)
            CR.Select()
            xlWorkBook.SaveAs(SaveFileDialog1.FileName)
            Clipboard.Clear()

            ReleaseExcel(CR)
            CR = Nothing
            ReleaseExcel(xlWorkSheet)
            xlWorkSheet = Nothing
            xlWorkBook.Close(False)
            ReleaseExcel(xlWorkBook)
            xlWorkBook = Nothing
            ReleaseExcel(xlWorkBooks)
            xlWorkBooks = Nothing
            xlExcel.Quit()
            ReleaseExcel(xlExcel)
            xlExcel = Nothing

            Dim OpenCheck As MsgBoxResult = MsgBox("Would you like to open the file?", MsgBoxStyle.YesNo, "Open File")
            Dim fPath As String = SaveFileDialog1.FileName
            If OpenCheck = MsgBoxResult.Yes Then
                Process.Start(fPath)
            End If

        End If
    End If

End Sub

Private Sub ReleaseExcel(ByVal O As Object)
    Try
        Do Until System.Runtime.InteropServices.Marshal.FinalReleaseComObject(O) = 0
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(O)
        Loop
    Catch ex As Exception
    Finally
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try
End Sub

I suspect the issue is in your ReleaseExcel() method. I'm not sure you can treat all objects the same. They may need to be disposed, released, closed, stopped, deleted, etc individually. — Casey Crookston, Feb 16 '16 at 19:42
Here's an example: http://stackoverflow.com/questions/3814026/how-to-release-excel-process — Casey Crookston, Feb 16 '16 at 19:43
Can you put a breakpoint at ReleaseExcel(CR), step into that method, and watch what happens? — Casey Crookston, Feb 16 '16 at 19:48
on that example they are essientially doing the same thing but I'm just passing each excel object to the releaseExcel sub instead of repeating code. when I step through it each object goes from a value of 0 to -1, except for the xlExcel which goes from 1 to -1 and then the variables get set to nothing — ExcelNoobie25, Feb 16 '16 at 20:28
I gotta give you props for this question. I'm working on a project that exports a datagridview to Excel. The other methods I found on SO involved looping through each and every cell. This C&P method is SOOOOO much faster! — Tim, Feb 17 '16 at 17:17

score 1 · Accepted Answer · answered Feb 16 '16 at 20:51

The following goes along with using only two dots. After the work is done excel is properly disposed of.

Option Strict On
Option Infer Off
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module Demo1
    Public Sub DoPaste()
        Dim FileName As String = IO.Path.Combine(Application.StartupPath, "SomeFile.xlsx")

        Dim SheetName As String = "Sheet1"

        Dim Proceed As Boolean = False
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlCells As Excel.Range = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheets = xlWorkBook.Sheets

        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetName Then

                Dim xlRange1 As Excel.Range = Nothing
                xlRange1 = xlWorkSheet.Range("A1:B6")
                xlRange1.Select()
                xlRange1.Copy()

                Dim xlDestination As Excel.Range = Nothing
                xlDestination = xlWorkSheet.Range("C1:D6")

                xlDestination.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone)

                Marshal.FinalReleaseComObject(xlDestination)
                xlDestination = Nothing

                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing

                xlWorkSheet.SaveAs(FileName)
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
                Exit For
            End If

        Next

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        ReleaseComObject(xlCells)
        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)

    End Sub
    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Module

Thanks Karen, I'm not sure exactly what did it but after doing some comparisons and making some adjustments it finally works! I'm going to mark your answer as correct since it got me to where I wanted to be. Thank you! — ExcelNoobie25, Feb 16 '16 at 21:35

Copying Datagridview to excel - leaves excel process open

1 Answers1