Zaggler Zaggler - 4 months ago 63
Vb.net Question

Excel application not quitting after calling quit

Hey guys I have a small problem that I can't seem to figure out. I am saving a DataGridView (it's contents) to an xls file. I have no problem in doing so except in my task manager its still showing up that it's running. I have called:

xlApp.Application.Quit()


This is declared as:

Dim xlApp As New excel.Application


This seems to not work, BUT this is the same way I quit when I let the user choose to export it to a Word Document. Im not sure where I am going wrong...

Here is my complete code

Imports Word = Microsoft.Office.Interop.Word
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
For x As Integer = 1 To 3500
DataGridView1.Rows.Add(New Object() {"r" & x.ToString & "c1", "r" & x.ToString & "c2", "r" & x.ToString & "c3", "r" & x.ToString & "c4", "r" & x.ToString & "c5"})
Next
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
exportToWord (DataGridView1)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
'Dim misValue As Object = System.Reflection.Missing.Value


xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)

xlApp.Visible = True

Dim headers = (From ch In DataGridView1.Columns _
Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
Select header.Value).ToArray()
Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)

Dim items() = (From r In DataGridView1.Rows _
Let row = DirectCast(r, DataGridViewRow) _
Where Not row.IsNewRow _
Select (From cell In row.Cells _
Let c = DirectCast(cell, DataGridViewCell) _
Select c.Value).ToArray()).ToArray()

Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
For Each a In items
Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
table &= String.Join(vbTab, t) & Environment.NewLine
Next
table = table.TrimEnd(CChar(Environment.NewLine))
Clipboard.SetText (table)

Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray

Dim range As excel.Range = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)

range.Select()
xlWorkSheet.Paste()

range.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
range.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
With range.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlMedium
End With
With range.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With range.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

'xlApp.Visible = True

xlWorkBook.SaveAs("C:\Users\CoDeXeR\Desktop\Word1.xls", True)
xlWorkBook.Close()
xlApp.Application.Quit()

ReleaseObject(xlWorkSheet) '<~~~ Added as per comment from deleted post
ReleaseObject (xlWorkBook)
ReleaseObject (xlApp)


End Sub

Public Sub exportToWord(ByVal dgv As DataGridView)
' Create Word Application
Dim oWord As Word.Application = DirectCast(CreateObject("Word.Application"), Word.Application)

' Create new word document
Dim oDoc As Word.Document = oWord.Documents.Add()


Dim headers = (From ch In dgv.Columns _
Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
Select header.Value).ToArray()
Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)

Dim items() = (From r In dgv.Rows _
Let row = DirectCast(r, DataGridViewRow) _
Where Not row.IsNewRow _
Select (From cell In row.Cells _
Let c = DirectCast(cell, DataGridViewCell) _
Select c.Value).ToArray()).ToArray()

Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
For Each a In items
Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
table &= String.Join(vbTab, t) & Environment.NewLine
Next
table = table.TrimEnd(CChar(Environment.NewLine))
Clipboard.SetText (table)

Dim oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, items.Count + 1, headers.Count)

oTable.Range.Paste()

'make the first row bold, fs 14 + change textcolor
oTable.Rows.Item(1).range.Font.Bold = &H98967E
oTable.Rows.Item(1).range.Font.Size = 14
oTable.Rows.Item(1).range.Font.Color = Word.WdColor.wdColorWhite

'change backcolor of first row
oTable.Rows.Item(1).range.Shading.Texture = Word.WdTextureIndex.wdTextureNone
oTable.Rows.Item(1).range.Shading.ForegroundPatternColor = Word.WdColor.wdColorAutomatic
oTable.Rows.Item(1).range.Shading.BackgroundPatternColor = Word.WdColor.wdColorLightBlue

''set table borders
'With oTable.Range.Tables(1)
' With .Borders(Word.WdBorderType.wdBorderLeft)
' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
' .Color = Word.WdColor.wdColorAutomatic
' End With
' With .Borders(Word.WdBorderType.wdBorderRight)
' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
' .Color = Word.WdColor.wdColorAutomatic
' End With
' With .Borders(Word.WdBorderType.wdBorderTop)
' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
' .Color = Word.WdColor.wdColorAutomatic
' End With
' With .Borders(Word.WdBorderType.wdBorderBottom)
' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
' .LineWidth = Word.WdLineWidth.wdLineWidth100pt
' .Color = Word.WdColor.wdColorAutomatic
' End With
' With .Borders(Word.WdBorderType.wdBorderHorizontal)
' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
' .LineWidth = Word.WdLineWidth.wdLineWidth050pt
' .Color = Word.WdColor.wdColorAutomatic
' End With
' With .Borders(Word.WdBorderType.wdBorderVertical)
' .LineStyle = Word.WdLineStyle.wdLineStyleSingle
' .LineWidth = Word.WdLineWidth.wdLineWidth050pt
' .Color = Word.WdColor.wdColorAutomatic
' End With
' .Borders(Word.WdBorderType.wdBorderDiagonalDown).LineStyle = Word.WdLineStyle.wdLineStyleNone
' .Borders(Word.WdBorderType.wdBorderDiagonalUp).LineStyle = Word.WdLineStyle.wdLineStyleNone
' .Borders.Shadow = False
'End With
' Save this word document
oDoc.SaveAs("C:\Users\CoDeXeR\Desktop\Word1.doc", True)
oDoc.Close()
oWord.Application.Quit()
'oWord.Visible = True

End Sub

Public Sub exportToExcel(ByVal dgv As DataGridView)

End Sub

Private Sub ReleaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub

End Class

Answer

Just Calling .Quit() will not remove the Application from memory. It is very important to close the objects after you are done with your coding. This ensures that all objects are released properly and nothing remains in the memory.

See this example

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some stuff Here

        '~~> Save the file
        xlWorkBook.SaveAs(Filename:="C:\Tutorial\SampleNew.xlsx", FileFormat:=51)

        '~~> Close the File
        xlWorkBook.Close()

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject (xlApp)
        releaseObject (xlWorkBook)
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

Also worth mentioning is the 2 DOT Rule.

If you love automating Excel from VB.Net then you might also want to have a look at this link.

FOLLOWUP

The problem is the 2 DOT Rule as I mentioned above. When you use the 2 DOT Rule (Ex: Excel.XlBordersIndex.xlDiagonalDown) then you have to do the Garbage Collection by using GC.Collect(). So All you need to do is add this part

    Finally
        GC.Collect()

in the Private Sub ReleaseObject(ByVal obj As Object)

Private Sub ReleaseObject(ByVal obj As Object)
    Try
        Dim intRel As Integer = 0
        Do
            intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        Loop While intRel > 0
        MsgBox("Final Released obj # " & intRel)
    Catch ex As Exception
        MsgBox("Error releasing object" & ex.ToString)
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

FINAL CODE (Tried And Tested)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim xlApp As New excel.Application
    Dim xlWorkBook As excel.Workbook
    Dim xlWorkSheet As excel.Worksheet
    Dim xlRange As excel.Range
    'Dim misValue As Object = System.Reflection.Missing.Value

    xlWorkBook = xlApp.Workbooks.Add
    xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)

    xlApp.Visible = True

    Dim headers = (From ch In DataGridView1.Columns _
                  Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
                  Select header.Value).ToArray()
    Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)

    Dim items() = (From r In DataGridView1.Rows _
          Let row = DirectCast(r, DataGridViewRow) _
          Where Not row.IsNewRow _
          Select (From cell In row.Cells _
              Let c = DirectCast(cell, DataGridViewCell) _
              Select c.Value).ToArray()).ToArray()

    Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
    For Each a In items
        Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
        table &= String.Join(vbTab, t) & Environment.NewLine
    Next
    table = table.TrimEnd(CChar(Environment.NewLine))
    Clipboard.SetText(table)

    Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray

    xlRange = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)

    xlRange.Select()
    xlWorkSheet.Paste()

    xlRange.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone
    xlRange.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone

    With xlRange.Borders(excel.XlBordersIndex.xlEdgeLeft)
        .LineStyle = excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = excel.XlBorderWeight.xlMedium
    End With
    With xlRange.Borders(excel.XlBordersIndex.xlEdgeTop)
        .LineStyle = excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = excel.XlBorderWeight.xlMedium
    End With
    With xlRange.Borders(excel.XlBordersIndex.xlEdgeBottom)
        .LineStyle = excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = excel.XlBorderWeight.xlMedium
    End With
    With xlRange.Borders(excel.XlBordersIndex.xlEdgeRight)
        .LineStyle = excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = excel.XlBorderWeight.xlMedium
    End With
    With xlRange.Borders(excel.XlBordersIndex.xlInsideVertical)
        .LineStyle = excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = excel.XlBorderWeight.xlThin
    End With
    With xlRange.Borders(excel.XlBordersIndex.xlInsideHorizontal)
        .LineStyle = excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = excel.XlBorderWeight.xlThin
    End With

    xlWorkBook.SaveAs(Filename:="C:\Users\Siddharth Rout\Desktop\Word1.xls", FileFormat:=56)
    xlWorkBook.Close()
    xlApp.Quit()

    ReleaseObject(xlRange)
    ReleaseObject(xlWorkSheet)
    ReleaseObject(xlWorkBook)
    ReleaseObject(xlApp)
End Sub


Private Sub ReleaseObject(ByVal obj As Object)
    Try
        Dim intRel As Integer = 0
        Do
            intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        Loop While intRel > 0
        MsgBox("Final Released obj # " & intRel)
    Catch ex As Exception
        MsgBox("Error releasing object" & ex.ToString)
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub