CODEWITHSUNDEEP
excelvba
DPA
DPA

Reputation: 7

Close a Workbook if opened

I would like an error handler to handle closing an Excel workbook that is not open.

I tried below code.

If Workbooks("Combo.xlsx").IsOpen Then 
     Workbooks("Combo.xlsx").Close SaveChanges:=False 
Else: Resume Next 
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical 
End If

This gives me an error message:

Run time Error: 9 Subscript Out of Range.

Upvotes: 0

Views: 18241

Answers (2)

Petter
Petter

Reputation: 341

I had the same problem that the On Error Resume Next didn't seem to work for this. My settings are only to break on unhandled errors so I can't understand why it keeps breaking when trying to close.

Anyway, I made this simple workaround subroutine to call when closing the workbook:

Private Sub closeWorkbookIfOpen(wb As Workbook)
    On Error GoTo NotOpen

    wb.Close

NotOpen:
End Sub

Not sure why the Resume Next doesn't work, but this seems to do the trick!

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166306

All you really need is

On Error Resume Next
Workbooks("Combo.xlsx").Close SaveChanges:=False
On Error Goto 0

You can ignore the error if there is no workbook open with that name.

Upvotes: 2

Related Questions