Close file Macro

skhou

Board Regular
Joined
May 22, 2007
Messages
83
Hi, I have used the following code to close my workbook. However, how do I adjust the code to catch an exception with a dialogue box, in the event the workbook is not open in the first place to close it? Thanks

Private Sub CloseDatabase_Button_Click()


Workbooks("Pricing Papers Received.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

'Workbooks("Pricing Papers Received.xls").Close

End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,406
Code:
Private Sub CloseDatabase_Button_Click()

    Dim wb As Workbook
    
    On Error Resume Next
        Set wb = Workbooks("Pricing Papers Received.xls")
    On Error GoTo 0
    
    If Not wb Is Nothing Then
        wb.Close SaveChanges:=True
    Else
        MsgBox "Pricing Papers Received.xls was not open."
    End If

End Sub
 

skhou

Board Regular
Joined
May 22, 2007
Messages
83
Hi,

Thanks for your help Alphafrog. The code works great. Are you able to explain briefly what the logic is doing with the Error Resume Next, as well asthe Is Nothing syntax?

thanks,

skhou
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,406
This sets the variable wb to the open workbook Pricing Papers Received.xls
Set wb = Workbooks("Pricing Papers Received.xls")

That line would normally error if there in no open workbook called Pricing Papers Received.xls

The On Error Resume Next suspends error checking. So the variable wb will be set if it is open or it won't be set if it is not.

Then the IF line tests if the variable wb is not Nothing (empty, blank, nada). That way you know if the workbook is open or not.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,346
Messages
5,641,598
Members
417,225
Latest member
LukiiMaxim96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top