[Webinar] Streamline your web hosting managementRegister Today

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

Killing Excel

I have an application that uses Excel to create reports in spreadsheets.

Everything works perfectly except at the end when I try to exit Excel using:

            xlBook.Application.Quit()

if there is a network error and the file could not be automatically saved, the line above is not run...

At the start of the application, I want to close any existing Excel sessions, so I made:


    Public Function KillExcel()
        Dim EmptyProcess() As Process = Process.GetProcessesByName("EXCEL.EXE")
        Dim DeadApp As Process
        For Each Victim In EmptyProcess
            DeadApp.Kill()
        Next
    End Function

but for some reason, it will not kill Excel processes, I have used this exact same code to terminate several other processes from other applications, so I know it works.

Is there any reason it is not working?  Is there a better way to kill all open Excel processes before running my application?



0
lgropper
Asked:
lgropper
1 Solution
 
Bob LearnedCommented:
Here is a class that I use to work with Excel.  Notice the use of Quit and Marshal.ReleaseComObject:

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core
Imports System.Runtime.InteropServices

Public Class ExcelWorksheet

  Private _app As Excel.Application
  Private _workbook As Excel.Workbook
  Private _worksheet As Excel.Worksheet

  Public Sub New()

    _app = New Excel.Application

  End Sub  'New


  Public Property ApplicationVisible() As Boolean
    Get
      Return _app.Visible
    End Get
    Set(ByVal Value As Boolean)
      _app.Visible = Value
    End Set
  End Property  'ApplicationVisible


  Public Property AssistantVisible() As Boolean
    Get
      Return _app.Assistant.Visible
    End Get
    Set(ByVal Value As Boolean)
      _app.Assistant.Visible = Value
    End Set
  End Property  'AssistantVisible


  Protected Overrides Sub Finalize()
    MyBase.Finalize()

    _workbook.Close(SaveChanges:=False)

    _app.Quit()

    Marshal.ReleaseComObject(_app)
    Marshal.ReleaseComObject(_workbook)
    Marshal.ReleaseComObject(_worksheet)

  End Sub   'Finalize


  Public Sub Save()

    _workbook.Save()

  End Sub  'Save


  Public Sub DeleteRow(ByVal row As Integer)

    If row < 1 OrElse row > _worksheet.Rows.Count Then
      Throw New ArgumentOutOfRangeException("Row must be 1.." & _worksheet.Rows.Count)
    End If

    CType(_worksheet.Rows(row), Excel.Range).Delete()

  End Sub


  Public Function GetWorksheetData(ByVal fileName As String, _
   ByVal sheetName As String, ByVal fromCell As String, ByVal toCell As String, _
   ByVal firstRowHeader As Boolean) As DataTable

    _workbook = _app.Workbooks.Open(fileName)

    _worksheet = _workbook.Worksheets(sheetName)

    Dim rangeSelect As Excel.Range = _worksheet.Range(fromCell, toCell)

    Dim rowCount As Integer = rangeSelect.Rows.Count
    Dim columnCount As Integer = rangeSelect.Columns.Count

    Dim tableNew As New System.Data.DataTable(sheetName)

    For colIndex As Integer = 1 To columnCount

      Dim colName As String = "Column" & colIndex

      If firstRowHeader Then
        colName = CType(rangeSelect.Cells(1, colIndex), Excel.Range).Value
      End If

      tableNew.Columns.Add(New DataColumn(colName, GetType(String)))

    Next colIndex

    For rowIndex As Integer = 1 To rowCount

      Dim rowNew As DataRow = tableNew.NewRow

      For colIndex As Integer = 1 To columnCount
        rowNew(colindex - 1) = CType(rangeSelect.Cells(rowIndex, colindex), Excel.Range).Value
      Next

      tableNew.Rows.Add(rowNew)

    Next

    Return tableNew

  End Function   'GetWorksheetData


  Public Property CellText(ByVal row As Integer, ByVal column As Integer) As String
    Get
      If row < 1 OrElse row > _worksheet.Rows.Count Then
        Throw New ArgumentOutOfRangeException("Row")
      End If
      If column < 1 OrElse column > _worksheet.Columns.Count Then
        Throw New ArgumentOutOfRangeException("Column")
      End If

      Dim cellRange As Excel.Range = _worksheet.Cells(row, column)
      Return cellRange.Value
    End Get
    Set(ByVal Value As String)
      If row < 1 OrElse row > _worksheet.Rows.Count Then
        Throw New ArgumentOutOfRangeException("Row")
      End If
      If column < 1 OrElse column > _worksheet.Columns.Count Then
        Throw New ArgumentOutOfRangeException("Column")
      End If

      Dim cellRange As Excel.Range = _worksheet.Cells(row, column)
      cellRange.Value = Value
    End Set
  End Property  'CellText


  Public Property CellFormula(ByVal row As Integer, ByVal column As Integer) As String
    Get
      If row < 1 OrElse row > _worksheet.Rows.Count Then
        Throw New ArgumentOutOfRangeException("Row")
      End If
      If column < 1 OrElse column > _worksheet.Columns.Count Then
        Throw New ArgumentOutOfRangeException("Column")
      End If

      Dim cellRange As Excel.Range = _worksheet.Cells(row, column)
      Return cellRange.Formula
    End Get
    Set(ByVal Value As String)
      If row < 1 OrElse row > _worksheet.Rows.Count Then
        Throw New ArgumentOutOfRangeException("Row")
      End If
      If column < 1 OrElse column > _worksheet.Columns.Count Then
        Throw New ArgumentOutOfRangeException("Column")
      End If

      Dim cellRange As Excel.Range = _worksheet.Cells(row, column)
      cellRange.Formula = Value
    End Set
  End Property  'CellFormula

End Class

Bob
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
"Is there any reason it is not working?"

Yes...DON'T include the ".EXE":

    Dim EmptyProcess() As Process = Process.GetProcessesByName("EXCEL")
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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