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

Question

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

Answer 1

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