Close All Excel Workbooks Except Active Workbook

[Maryz]

I’d like to place code in my Workbook_Open event that determines if there are any other Excel files opened in the current instance of Excel, and if so, closes them without saving leaving only the active workbook open. My end goal is to have only my file open in an instance of Excel by itself. I presume that I cannot develop code that opens up my workbook in a new instance of Excel from any other Excel file that may already be open. This would be ideal, but theoretically I cannot see how it can be accomplished as the workbook must already be opened before the Worbook_Open code can execute. Any help is much appreciated.

 

[Mat]

Hi Maryz,

This code should do what you want :

Private Sub Workbook_Open()
Dim WB As Workbook, MyWB As Workbook

Set MyWB = ActiveWorkbook

For Each WB In Workbooks
If WB.Name <> MyWB.Name Then WB.Close
Next

Exit Sub

EDIT: This one may be better :

Private Sub Workbook_Open()
Dim WB As Workbook

For Each WB In Workbooks
     If Not (WB Is ActiveWorkbook) Then WB.Close
Next

End Sub

 

[Maryz]

Thank you Mat. That did the trick.

 

[DrDoyle]

20 years later..... it worked for me too! hahahhaha THANKS!!
All the other solutions I was finding were wayyyyyy too complicated. This helped me get around an issue where I had a VBA app open up, and hide its workbook, but anything inputted in the app would then communicate with a workbook that was already open so you would have to make sure all other workbooks were closed before opening this VBA app. This code made it so it just closes the other workbooks. THANK YOU!!