Macro to close all open workbooks except those of specific filename 1

[shawnmandel]

How do I create a macro to close all open workbooks except those of a specific filename(s). For example, I only want to keep Workbook1.xls and Workbook2a.xls open while I want to close every other excel file.

Any help would be greatly appreciated.

Thanks,
Shawn

 

[RollinAgain]

Try the following code. Just replace "Book1" and "Book2" in the code with the actual names of the workbooks you want to keep open. The workbooks names ARE case sensitive so make sure to spell them exactly the way they are named without include the .xls extension.


Public Sub Workbooks()


Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name <> &quot;Book1&quot; And wb.Name <> &quot;Book3&quot; Then
wb.Close
End If

Next wb



End Sub







Rollin Again

 

[SkipVought]

Shawn,

Consider using the Select Case structure

    For Each wb In Application.Workbooks
      With wb
        Select Case  .Name         
          Case "Book1", "Book3"
            .Close
        End Select
      End with
    Next wb

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[shawnmandel]

Skip, I intended to have my &quot;Book1&quot; & &quot;Book3&quot; files remaining open at the end of the subroutine. It appears that &quot;Book1&quot; & &quot;Book3&quot; are closed.

Rolling, your suggestion almost worked for me since &quot;Book1&quot; remains open, but &quot;Book3&quot; closes.


The end result of the subroutine should keep only &quot;Book1&quot; and &quot;Book3&quot; open while closing all other workbooks.


Thanks,
Shawn

 

[SkipVought]

It should seem obvious what to do, yes?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[shawnmandel]

Hi Skip,

I though the following change that I made would work, but I got an error:

For Each wb Not In Application.Workbooks
With wb
Select Case .Name
Case &quot;Book1&quot;, &quot;Book3&quot;
.Close
End Select
End with
Next wb


The &quot;Not In&quot; clause doesn't appear to be valid. It looked obvious to me.

Thanks,
Shawn

 

[SkipVought]

For Each wb Not In Application.Workbooks
      With wb
        Select Case  .Name         
          Case "Book1", "Book3" 'leave open
          Case Else             'close
            .Close
        End Select
      End with
Next wb

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[shawnmandel]

Hi Skip,

You final suggestion helped a great deal. Thanks very much for your help

Kind regards,
Shawn

 

[SkipVought]

I did not notice the NO IN structure

For Each wb In Application.Workbooks
      With wb
        Select Case  .Name         
          Case "Book1", "Book3" 'leave open
          Case Else             'close
            .Close
        End Select
      End with
Next wb

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Scrooby]

Hi,
Interesting topic... I tried this last code to close some specific Powerpoint presentations and it worked fine with ppt 97, but fails to work with ppt 2003 :S.
My program really needs to work in different PPT versions! Can you help me?


Dim aa As Presentation
For Each aa In Application.Presentations
With aa
Select Case .Name
Case &quot;presentation1&quot;, &quot;presentation2&quot;, &quot;presentation3&quot;
.Close
End Select
End With
Next aa


Huib

 

[SkipVought]

Wish I could help -- don't have Excel 2003

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884