Close all workbooks and keep excel running

Discussion in 'Microsoft Excel Misc' started by Scott Hutslar, Jun 25, 2008.

  1. 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 Hutslar, Jun 25, 2008
    #1
    1. Advertisements

  2. Scott Hutslar

    Tim879 Guest

    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
    > 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?
     
    Tim879, Jun 25, 2008
    #2
  3. 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
    > > 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 Hutslar, Jun 25, 2008
    #3
  4. Scott Hutslar

    Thomas [PBD] Guest

    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
    > > > 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?

    > >
    > >
     
    Thomas [PBD], Jun 25, 2008
    #4
  5. 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
    > > > > 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 Hutslar, Jun 25, 2008
    #5
  6. Scott Hutslar

    Gord Dibben Guest

    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
    <> 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
    >> > 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?

    >>
    >>
     
    Gord Dibben, Jun 25, 2008
    #6

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    0
    Views:
    265
    Guest
    Feb 26, 2004
  2. Guest
    Replies:
    0
    Views:
    355
    Guest
    Jun 10, 2005
  3. Guest

    suddenly my excel workbooks are "shared workbooks"

    Guest, Aug 28, 2005, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    206
    Conrad Carlberg
    Aug 28, 2005
  4. Guest
    Replies:
    1
    Views:
    716
    Monza
    Nov 29, 2006
  5. Glen@DFR
    Replies:
    5
    Views:
    464
    Dave Peterson
    Jul 10, 2008
Loading...

Share This Page