Please can somebody help with the code to close all open workbooks without
referring to them by name. I often have to open about 30 workbooks and
closing them down is a waste of time.

Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I need
all the workbooks saved but without the Check Compatibility checked. Please
help

I do not have 2007 here to test my code on but here is something that should
be close..

sub CloseBooks()
dim wbk as workbook

for each wbk in workbooks
wbk.close SaveChanges:=true
next wbk
exit sub

--
HTH...

Jim Thomlinson


"MurrayB" wrote:

> Please can somebody help with the code to close all open workbooks without
> referring to them by name. I often have to open about 30 workbooks and
> closing them down is a waste of time.
>
> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I need
> all the workbooks saved but without the Check Compatibility checked. Please
> help
>
>

On Sep 16, 3:22*pm, "MurrayB" <murraybarnet...@hotmail.com> wrote:
> Please can somebody help with the code to close all open workbooks without
> referring to them by name. I often have to open about 30 workbooks and
> closing them down is a waste of time.
>
> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I need
> all the workbooks saved but without the Check Compatibility checked. Please
> help

MurrayB-
The Close All command is still available in Excel 2007 and can be
added to the QAT. It will prompt you to save all workbooks that have
been updated. However, there is no real easy way to avoid the
compatability checker. For workbooks I use frequently, I uncheck the
option so that it doesn't keep popping up on every save. I am unaware
of any global setting. Therefore, it appears it's a one-time uncheck
for each workbook.

Regards,
Excel.Instructor (Ed2Go.com/Advanced Excel)

Sub CLOSE_ALL()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In Application.Workbooks
w.Save
w.Close SaveChanges:=True
Next w
'uncomment line below to automatically leave
'Application.Quit
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"MurrayB" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Please can somebody help with the code to close all open workbooks without
> referring to them by name. I often have to open about 30 workbooks and
> closing them down is a waste of time.
>
> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I need
> all the workbooks saved but without the Check Compatibility checked.
> Please help

Hi Jim

I tried the code but it only closes my Personal Macro Workbook. I need to
keep that sheet open but close all my "data" workbooks. Any ideas?

Thanks
Murray


"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:7434F922-162F-4763-87A8-(E-Mail Removed)...
>I do not have 2007 here to test my code on but here is something that
>should
> be close..
>
> sub CloseBooks()
> dim wbk as workbook
>
> for each wbk in workbooks
> wbk.close SaveChanges:=true
> next wbk
> exit sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "MurrayB" wrote:
>
>> Please can somebody help with the code to close all open workbooks
>> without
>> referring to them by name. I often have to open about 30 workbooks and
>> closing them down is a waste of time.
>>
>> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I need
>> all the workbooks saved but without the Check Compatibility checked.
>> Please
>> help
>>
>>

Hi Don

I tried the code but it only closes my Personal Macro Workbook. I need to
keep that workbook open but close all my "data" workbooks. Any ideas?

Thanks
Murray

"Don Guillett" <(E-Mail Removed)> wrote in message
news:%235mQT%(E-Mail Removed)...
> Sub CLOSE_ALL()
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> For Each w In Application.Workbooks
> w.Save
> w.Close SaveChanges:=True
> Next w
> 'uncomment line below to automatically leave
> 'Application.Quit
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "MurrayB" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Please can somebody help with the code to close all open workbooks
>> without referring to them by name. I often have to open about 30
>> workbooks and closing them down is a waste of time.
>>
>> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I need
>> all the workbooks saved but without the Check Compatibility checked.
>> Please help
>

Try it this way

Sub CLOSE_ALL()
>> Application.ScreenUpdating = False
>> Application.DisplayAlerts = False
>> For Each w In Application.Workbooks
if w.name<>"Personal.xls" then
>> w.Save
>> w.Close SaveChanges:=True
end if
>> Next w
>> 'uncomment line below to automatically leave
>> 'Application.Quit
>> End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"MurrayB" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Don
>
> I tried the code but it only closes my Personal Macro Workbook. I need to
> keep that workbook open but close all my "data" workbooks. Any ideas?
>
> Thanks
> Murray
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:%235mQT%(E-Mail Removed)...
>> Sub CLOSE_ALL()
>> Application.ScreenUpdating = False
>> Application.DisplayAlerts = False
>> For Each w In Application.Workbooks
>> w.Save
>> w.Close SaveChanges:=True
>> Next w
>> 'uncomment line below to automatically leave
>> 'Application.Quit
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "MurrayB" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Please can somebody help with the code to close all open workbooks
>>> without referring to them by name. I often have to open about 30
>>> workbooks and closing them down is a waste of time.
>>>
>>> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I
>>> need all the workbooks saved but without the Check Compatibility
>>> checked. Please help
>>
>

Hi Don

Thanks for that. The below procedure works but about 50% of the time it
still only closes the Personal.xlsb workbook and nothing else. If I reopen
that workbook and run the sub again, it then closes all the other workbooks.
Sometimes it closes the Personal.xlsb book at the same time and sometimes
not.

Below is the code as I am using it:

Sub CloseAll()
Dim w As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In Application.Workbooks
If w.Name <> "Personal.xlsb" Then
w.Save
w.Close SaveChanges:=True
End If
Next w
'uncomment line below to automatically leave
'Application.Quit
End Sub

Any further ideas?
"Don Guillett" <(E-Mail Removed)> wrote in message
news:OYa$(E-Mail Removed)...
> Try it this way
>
> Sub CLOSE_ALL()
>>> Application.ScreenUpdating = False
>>> Application.DisplayAlerts = False
>>> For Each w In Application.Workbooks
> if w.name<>"Personal.xls" then
>>> w.Save
>>> w.Close SaveChanges:=True
> end if
>>> Next w
>>> 'uncomment line below to automatically leave
>>> 'Application.Quit
>>> End Sub
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "MurrayB" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi Don
>>
>> I tried the code but it only closes my Personal Macro Workbook. I need to
>> keep that workbook open but close all my "data" workbooks. Any ideas?
>>
>> Thanks
>> Murray
>>
>> "Don Guillett" <(E-Mail Removed)> wrote in message
>> news:%235mQT%(E-Mail Removed)...
>>> Sub CLOSE_ALL()
>>> Application.ScreenUpdating = False
>>> Application.DisplayAlerts = False
>>> For Each w In Application.Workbooks
>>> w.Save
>>> w.Close SaveChanges:=True
>>> Next w
>>> 'uncomment line below to automatically leave
>>> 'Application.Quit
>>> End Sub
>>>
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "MurrayB" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Please can somebody help with the code to close all open workbooks
>>>> without referring to them by name. I often have to open about 30
>>>> workbooks and closing them down is a waste of time.
>>>>
>>>> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I
>>>> need all the workbooks saved but without the Check Compatibility
>>>> checked. Please help
>>>
>>
>

If this code is in personal.xlsb, then as soon as that workbook closes, the code
stops. And any workbooks still open will be left open.

And the comparison "If w.Name <> "Personal.xlsb" Then" is case sensitive.

So maybe...

Sub CloseAll()
Dim w As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In Application.Workbooks
If lcase(w.Name) <> lcase("Personal.xlsb") Then
w.Save
w.Close SaveChanges:=True
End If
Next w
'uncomment line below to automatically leave
'Application.Quit
End Sub

And verify the extension on your personal.* workbook. You'll want to match it
in your code.

MurrayB wrote:
>
> Hi Don
>
> Thanks for that. The below procedure works but about 50% of the time it
> still only closes the Personal.xlsb workbook and nothing else. If I reopen
> that workbook and run the sub again, it then closes all the other workbooks.
> Sometimes it closes the Personal.xlsb book at the same time and sometimes
> not.
>
> Below is the code as I am using it:
>
> Sub CloseAll()
> Dim w As Workbook
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> For Each w In Application.Workbooks
> If w.Name <> "Personal.xlsb" Then
> w.Save
> w.Close SaveChanges:=True
> End If
> Next w
> 'uncomment line below to automatically leave
> 'Application.Quit
> End Sub
>
> Any further ideas?
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:OYa$(E-Mail Removed)...
> > Try it this way
> >
> > Sub CLOSE_ALL()
> >>> Application.ScreenUpdating = False
> >>> Application.DisplayAlerts = False
> >>> For Each w In Application.Workbooks
> > if w.name<>"Personal.xls" then
> >>> w.Save
> >>> w.Close SaveChanges:=True
> > end if
> >>> Next w
> >>> 'uncomment line below to automatically leave
> >>> 'Application.Quit
> >>> End Sub
> >
> >
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "MurrayB" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Hi Don
> >>
> >> I tried the code but it only closes my Personal Macro Workbook. I need to
> >> keep that workbook open but close all my "data" workbooks. Any ideas?
> >>
> >> Thanks
> >> Murray
> >>
> >> "Don Guillett" <(E-Mail Removed)> wrote in message
> >> news:%235mQT%(E-Mail Removed)...
> >>> Sub CLOSE_ALL()
> >>> Application.ScreenUpdating = False
> >>> Application.DisplayAlerts = False
> >>> For Each w In Application.Workbooks
> >>> w.Save
> >>> w.Close SaveChanges:=True
> >>> Next w
> >>> 'uncomment line below to automatically leave
> >>> 'Application.Quit
> >>> End Sub
> >>>
> >>> --
> >>> Don Guillett
> >>> Microsoft MVP Excel
> >>> SalesAid Software
> >>> (E-Mail Removed)
> >>> "MurrayB" <(E-Mail Removed)> wrote in message
> >>> news:(E-Mail Removed)...
> >>>> Please can somebody help with the code to close all open workbooks
> >>>> without referring to them by name. I often have to open about 30
> >>>> workbooks and closing them down is a waste of time.
> >>>>
> >>>> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I
> >>>> need all the workbooks saved but without the Check Compatibility
> >>>> checked. Please help
> >>>
> >>
> >

--

Dave Peterson

And if the code is in a different workbook, you'll want to avoid closing that
workbook too early, also.

Option Explicit
Sub CloseAll()
Dim w As Workbook
For Each w In Application.Workbooks
If LCase(w.Name) = LCase("Personal.xlsb") _
Or LCase(w.Name) = LCase(ThisWorkbook.Name) Then
'do nothing
Else
w.Close savechanges:=True 'false?
End If
Next w

'close thisworkbook, too?
ThisWorkbook.Close savechanges:=True 'false
'uncomment line below to automatically leave
'Application.Quit
End Sub

Ps. This kind of thing would scare the heck out of me. I wouldn't want to save
a workbook that shouldn't be saved--and I wouldn't want to close w/o saving a
workbook that should be saved.

And I can't imagine ever having code smart enough to know what should be done to
each of my open workbooks.

I wouldn't use it.



Dave Peterson wrote:
>
> If this code is in personal.xlsb, then as soon as that workbook closes, the code
> stops. And any workbooks still open will be left open.
>
> And the comparison "If w.Name <> "Personal.xlsb" Then" is case sensitive.
>
> So maybe...
>
> Sub CloseAll()
> Dim w As Workbook
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> For Each w In Application.Workbooks
> If lcase(w.Name) <> lcase("Personal.xlsb") Then
> w.Save
> w.Close SaveChanges:=True
> End If
> Next w
> 'uncomment line below to automatically leave
> 'Application.Quit
> End Sub
>
> And verify the extension on your personal.* workbook. You'll want to match it
> in your code.
>
> MurrayB wrote:
> >
> > Hi Don
> >
> > Thanks for that. The below procedure works but about 50% of the time it
> > still only closes the Personal.xlsb workbook and nothing else. If I reopen
> > that workbook and run the sub again, it then closes all the other workbooks.
> > Sometimes it closes the Personal.xlsb book at the same time and sometimes
> > not.
> >
> > Below is the code as I am using it:
> >
> > Sub CloseAll()
> > Dim w As Workbook
> >
> > Application.ScreenUpdating = False
> > Application.DisplayAlerts = False
> > For Each w In Application.Workbooks
> > If w.Name <> "Personal.xlsb" Then
> > w.Save
> > w.Close SaveChanges:=True
> > End If
> > Next w
> > 'uncomment line below to automatically leave
> > 'Application.Quit
> > End Sub
> >
> > Any further ideas?
> > "Don Guillett" <(E-Mail Removed)> wrote in message
> > news:OYa$(E-Mail Removed)...
> > > Try it this way
> > >
> > > Sub CLOSE_ALL()
> > >>> Application.ScreenUpdating = False
> > >>> Application.DisplayAlerts = False
> > >>> For Each w In Application.Workbooks
> > > if w.name<>"Personal.xls" then
> > >>> w.Save
> > >>> w.Close SaveChanges:=True
> > > end if
> > >>> Next w
> > >>> 'uncomment line below to automatically leave
> > >>> 'Application.Quit
> > >>> End Sub
> > >
> > >
> > > --
> > > Don Guillett
> > > Microsoft MVP Excel
> > > SalesAid Software
> > > (E-Mail Removed)
> > > "MurrayB" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> Hi Don
> > >>
> > >> I tried the code but it only closes my Personal Macro Workbook. I need to
> > >> keep that workbook open but close all my "data" workbooks. Any ideas?
> > >>
> > >> Thanks
> > >> Murray
> > >>
> > >> "Don Guillett" <(E-Mail Removed)> wrote in message
> > >> news:%235mQT%(E-Mail Removed)...
> > >>> Sub CLOSE_ALL()
> > >>> Application.ScreenUpdating = False
> > >>> Application.DisplayAlerts = False
> > >>> For Each w In Application.Workbooks
> > >>> w.Save
> > >>> w.Close SaveChanges:=True
> > >>> Next w
> > >>> 'uncomment line below to automatically leave
> > >>> 'Application.Quit
> > >>> End Sub
> > >>>
> > >>> --
> > >>> Don Guillett
> > >>> Microsoft MVP Excel
> > >>> SalesAid Software
> > >>> (E-Mail Removed)
> > >>> "MurrayB" <(E-Mail Removed)> wrote in message
> > >>> news:(E-Mail Removed)...
> > >>>> Please can somebody help with the code to close all open workbooks
> > >>>> without referring to them by name. I often have to open about 30
> > >>>> workbooks and closing them down is a waste of time.
> > >>>>
> > >>>> Also, I use Excel 2007 but the workbooks are mostly in Excel 2003. I
> > >>>> need all the workbooks saved but without the Check Compatibility
> > >>>> checked. Please help
> > >>>
> > >>
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson