excelcrashevent-logforceclosewindows-error-reporting

How to automate the forced closure of excel, followed by a restart?


Occasionally excel freezes or crashes while performing some long running VBA code (runs 24hr/day) and I'm examining ways to handle the restart of excel programmatically. Any advice on best way to go about this would be appreciated.

One idea i have is to write a dotnet app that might be potentially triggered by, say, a windows logging error event such as 'Event Name: APPCRASH' and 'Faulting application EXCEL.EXE', but i have no idea if this is feasible/sensible way to go about it.

If it happens to be a sensible approach, i would envisage the app potentially performing the following tasks;

1) Auto close/cancel any windows error popup boxes such as "Do you want to send more information about the problem?", "Microsoft Excel has stopped working", or "Microsoft Excel is trying to recover your information."

2) Force close excel if still open (eg if hanging), as well as killing any excel.exe processes

3) Restart excel

Is is possible to write such an app in dotnet? What references/tags should i be looking for to find further information?

Thanks, Yug


Solution

  • After some digging, I pieced together the following solution. The vbs code below will force taskkill any outstanding excel.exe processes, and then opens the stated excel file (from which vba code can be restarted automatically from a worksheet_open event).

    1) Turn off Windows error reporting: Start, search 'Problem Reports and Solutions', change settings, advanced settings, Turn off problem reporting

    2) Disable auto recovery for excel: Within the WB in question, click; file, options, save, disable file recovery for this WB only

    3) Under windows event viewer, application logs, highlight the error (faulting excel app) , right click 'create a basic task', run application/script, and enter the chosen name of the file in 4).

    4) Paste the following code into text file and save as a vbScript file (.vbs)

    Option Explicit
    
    KillProcesses
    ExcelRestart
    
    Sub ExcelRestart()    
      Dim xlApp 
      Dim xlBook 
      Set xlApp = CreateObject("Excel.Application") 
      xlApp.DisplayAlerts = False  
      Set xlBook = xlApp.Workbooks.Open("C:\...\test.xlsx")
      xlApp.visible = True 
      xlBook.activate
      Set xlBook = Nothing
      Set xlApp = Nothing
    End sub
    
    Sub KillProcesses
      On error resume next
      Dim objWMIService, WshShell
      Dim proc, procList
      Dim strComputer, strCommand
      strCommand = "taskkill /F /IM excel.exe"   
      strComputer = "."
      Set WshShell = WScript.CreateObject("WScript.Shell") 
      Set objWMIService = GetObject("winmgmts:"& "{impersonationLevel=impersonate}!\\"& strComputer & "\root\cimv2")
      Set procList = objWMIService.ExecQuery("SELECT * FROM Win32_Process WHERE Name = 'excel.exe'")
      For Each proc In procList
          WshShell.run strCommand, 0, TRUE
      Next
    
    End sub