Could someone please let me know if the following simple VBScript is correct? It is supposed to close Excel after other processes have run (and left Excel open), but it doesn't work.
Set MyApp = CreateObject("Excel.Application")
MyApp.Quit
3 Answers
CreateObject creates a new object. If I understand your question correctly you want to attach to already running (orphaned) Excel processes to terminate them. You can do that with GetObject:
On Error Resume Next
Do
Set xl = GetObject(, "Excel.Application")
status = Err.Number
If status = 0 Then
For Each wb in xl.Workbooks
wb.Close False 'discard changes in open workbooks
Next
xl.Quit
ElseIf status <> 429 Then
WScript.Echo Err.Number & ": " & Err.Description
WScript.Quit 1
End If
Until status = 429
On Error Goto 0
Note that this will try to close all running Excel instances, discarding all changes in open workbooks. If you want it to save changes in open workbooks change the argument of the Close method to True. If you have Excel instances you want to keep running, you need to add code to exclude them from being closed.
Note also, that this will not forcibly terminate unresponsive instances. You'd need to kill the process for that:
Set wmi = GetObject("winmgmts://root/cimv2")
For Each xl In wmi.ExecQuery("SELECT * FROM Win32_Process WHERE Name = 'excel.exe'")
xl.Terminate
Next
Comments
Try this please.
ThisWorkbook.Saved = True
Application.Quit
Comments
CreateObject creates a COM object, so your
Set MyApp = CreateObject("Excel.Application")
starts a new Excel process. Use GetObject to "retrieve an existing object with the specified ProgID". See this for theory and praxis.
.VBSfile? For troubleshooting purposes I usually put this code around the outside:MsgBox "Start"thenMsgBox "Complete"which at least tells me the script ran. If you also addMsgBox Myapp.Nameafter you setMyAppit will also tell you something. But the real problem is that you are usingCreateObjectwhich simply instantiates a new excel. It doesn't get existing ones.