CODEWITHSUNDEEP
excelvbams-word
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

VBA: How to close Excel.Application after a crash

My Word macro uses this code to open and access an Excel workbook:

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("C:\Folder\Filename.xls")   

 ' Main procedure

exWb.Close
Set exWb = Nothing

objExcel.Quit
Set objExcel = Nothing

Since I'm still debugging, the macro often stops before reaching the Close and Quit commands, leaving an instance of Excel and the workbook open in the background.

I can sometimes close them by opening Task Manager and ending the tasks, but I doubt that's the best solution. Is there a macro I can write in Word that closes all open Workbooks and quits all (background) instances of Excel?

Upvotes: 1

Views: 2377

Answers (1)

A.S.H
A.S.H

Reputation: 29352

Following is the traditional way to deal with such anomalies:

Sub MySub()
    Dim objExcel As Excel.Application, exWb As Excel.Workbook
    On Error GoTo Cleanup

    Set objExcel = New Excel.Application
    Set exWb = objExcel.Workbooks.Open("C:\Folder\Filename.xls")

    ' Main procedure

Cleanup:
    If Err.number <> 0 Then MsgBox "Error " & Err.number & ": " & Err.Description
    If Not exWb Is Nothing Then exWb.Close False
    If Not objExcel Is Nothing Then objExcel.Quit
End Sub

Upvotes: 5

Related Questions