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
CodePudding user response:
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