Close file Macro

[skhou]

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

 

[AlphaFrog]

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]

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]

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.