Is there a way to close all workbooks without exiting excel? I have found
where you can exit excel and it will close all workbooks. I have a scenario
where it is desirable to close all open workbooks and keep excel running.

Anyone know of a way of doing this without closing each workbook individually?

You can use this macro:
Sub Close_All_Open_Workbooks()
Dim I

For I = 1 To Application.Workbooks.Count
Application.Workbooks(I).Close
Next

End Sub


On Jun 25, 8:49*am, Scott Hutslar <Scott
Huts...@discussions.microsoft.com> wrote:
> Is there a way to close all workbooks without exiting excel? *I have found
> where you can exit excel and it will close all workbooks. *I have a scenario
> where it is desirable to close all open workbooks and keep excel running.*
>
> Anyone know of a way of doing this without closing each workbook individually?

Thanks for the reply Tim. This is a good way of doing this if we create a
global macro for all users to execute. Is there a way provided by Excel for
an end user to do this? I guess I am looking for a Ribbon path for doing
this.

"Tim879" wrote:

> You can use this macro:
> Sub Close_All_Open_Workbooks()
> Dim I
>
> For I = 1 To Application.Workbooks.Count
> Application.Workbooks(I).Close
> Next
>
> End Sub
>
>
> On Jun 25, 8:49 am, Scott Hutslar <Scott
> Huts...@discussions.microsoft.com> wrote:
> > Is there a way to close all workbooks without exiting excel? I have found
> > where you can exit excel and it will close all workbooks. I have a scenario
> > where it is desirable to close all open workbooks and keep excel running.
> >
> > Anyone know of a way of doing this without closing each workbook individually?
>
>

Scott,

Does File>Close not work for this purpose? It will close all open Workbooks
and leave the Excel application open.

--
--Thomas [PBD]
Working hard to make working easy.


"Scott Hutslar" wrote:

> Thanks for the reply Tim. This is a good way of doing this if we create a
> global macro for all users to execute. Is there a way provided by Excel for
> an end user to do this? I guess I am looking for a Ribbon path for doing
> this.
>
> "Tim879" wrote:
>
> > You can use this macro:
> > Sub Close_All_Open_Workbooks()
> > Dim I
> >
> > For I = 1 To Application.Workbooks.Count
> > Application.Workbooks(I).Close
> > Next
> >
> > End Sub
> >
> >
> > On Jun 25, 8:49 am, Scott Hutslar <Scott
> > Huts...@discussions.microsoft.com> wrote:
> > > Is there a way to close all workbooks without exiting excel? I have found
> > > where you can exit excel and it will close all workbooks. I have a scenario
> > > where it is desirable to close all open workbooks and keep excel running.
> > >
> > > Anyone know of a way of doing this without closing each workbook individually?
> >
> >

Thanks Thomas. Close just closes the current (active) workbook. But your
suggestion prompted me to customize my tool bar and add a "Close All" button.
This can be found in the commands "Not in the Ribbon".

Thanks everyone for your help!

"Thomas [PBD]" wrote:

> Scott,
>
> Does File>Close not work for this purpose? It will close all open Workbooks
> and leave the Excel application open.
>
> --
> --Thomas [PBD]
> Working hard to make working easy.
>
>
> "Scott Hutslar" wrote:
>
> > Thanks for the reply Tim. This is a good way of doing this if we create a
> > global macro for all users to execute. Is there a way provided by Excel for
> > an end user to do this? I guess I am looking for a Ribbon path for doing
> > this.
> >
> > "Tim879" wrote:
> >
> > > You can use this macro:
> > > Sub Close_All_Open_Workbooks()
> > > Dim I
> > >
> > > For I = 1 To Application.Workbooks.Count
> > > Application.Workbooks(I).Close
> > > Next
> > >
> > > End Sub
> > >
> > >
> > > On Jun 25, 8:49 am, Scott Hutslar <Scott
> > > Huts...@discussions.microsoft.com> wrote:
> > > > Is there a way to close all workbooks without exiting excel? I have found
> > > > where you can exit excel and it will close all workbooks. I have a scenario
> > > > where it is desirable to close all open workbooks and keep excel running.
> > > >
> > > > Anyone know of a way of doing this without closing each workbook individually?
> > >
> > >

In earlier versions CTRL + w closed all workbooks but user is asked about
saving.


Gord Dibben MS Excel MVP

On Wed, 25 Jun 2008 06:10:01 -0700, Scott Hutslar
<(E-Mail Removed)> wrote:

>Thanks for the reply Tim. This is a good way of doing this if we create a
>global macro for all users to execute. Is there a way provided by Excel for
>an end user to do this? I guess I am looking for a Ribbon path for doing
>this.
>
>"Tim879" wrote:
>
>> You can use this macro:
>> Sub Close_All_Open_Workbooks()
>> Dim I
>>
>> For I = 1 To Application.Workbooks.Count
>> Application.Workbooks(I).Close
>> Next
>>
>> End Sub
>>
>>
>> On Jun 25, 8:49 am, Scott Hutslar <Scott
>> Huts...@discussions.microsoft.com> wrote:
>> > Is there a way to close all workbooks without exiting excel? I have found
>> > where you can exit excel and it will close all workbooks. I have a scenario
>> > where it is desirable to close all open workbooks and keep excel running.
>> >
>> > Anyone know of a way of doing this without closing each workbook individually?
>>
>>