Excel vba kill an invisible application after an error

Question

I have an application that first creates an invisible application:

        Dim ExcelApp As Object
        Set ExcelApp = CreateObject("Excel.Application")
        ExcelApp.Visible = False
        ExcelApp.ScreenUpdating = False
        ExcelApp.DisplayAlerts = False
        ExcelApp.EnableEvents = False

And then proceeds to use it to open files invisibly:

      Do While fileTitle <> ""
          Dim dataWorkbook As Workbook
          Set dataWorkbook = ExcelApp.Application.Workbooks.Open(folderPath & fileTitle)

At the end of the operations with the file macros closes the file:

          dataWorkbook.Close
          fileTitle = Dir()
         Loop

At the end of the sub macros closes the application:

      ExcelApp.Quit
      Set ExcelApp = Nothing
End Sub

However, if an error occurs before the file is closed, the invisible file and the application would never be closed and would continue to linger in operation systems not only eating away the memory and resources, but also preventing from doing any operations with the opened filed - renaming, opening or editting it.

I wonder if there is a way to close both the file and the application if an error occurs - in the current macros or to create a separate macros that detects invisible applications no variable points to and closes it.

Answer 1

At the top of your procedure use an error handler like

Set ExcelApp = CreateObject("Excel.Application")
On Error Goto CLOSE_FILE_ON_ERROR
  'With that line you tell VBA to jump to the closing part if an error happens

and use this goto marker before you close the file.

CLOSE_FILE_ON_ERROR:
    ExcelApp.Quit
End Sub

Note: You don't need to Set ExcelApp = Nothing because Excel does this automatically on End Sub.

---

Edit due to comments

If you need to show error messages or something then your code has to be extended like this:

    ExcelApp.Quit 'This is needed to regularly quit if there is no error
    Exit Sub 'Don't run into error handling if there was no exeption

CLOSE_FILE_ON_ERROR:
    Application.StatusBar = "Error occured"
    ExcelApp.Quit 'This is needed to quit after an exeption if there is an error
End Sub

Answer 2

You can try the below code where I kill the application like Excel, Chrome and Internet Explorer. You can use the below code with the On Error Goto error handler.

Public Function fnkilltask()
    Dim objWMIService
    Dim colProcessList
    Dim objProcess
    Dim strComputer

    strComputer = "." 
    Set objWMIService = GetObject("winmgmts://./root/cimv2")  ' Task mgr
    Set colProcessList = objWMIService.ExecQuery ("Select * from Win32_Process Where Name in ('EXCEL.EXE','Chrome.exe','iexplore.exe') ") 

    For Each objProcess in colProcessList 
        objProcess.Terminate() 
    Next
End Function