Code to close many open workbooks

[MurrayB]

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

 

[Jim Thomlinson]

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

 

[Excel.Instructor]

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)

 

[Don Guillett]

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

 

[MurrayB]

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

 

[MurrayB]

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]

Try it this way

Sub CLOSE_ALL()

 

[MurrayB]

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?

 

[Dave Peterson]

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.

 

[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.

 

[MurrayB]

Hi Dave

Thanks for your help. A quick question - what is the general rule wrt to
workbook names in macros regarding letter case? Or is it merely based on how
I have saved the name for a workbook? For instance, the code below is
looking for lower case. My Personal sheet calls itself PERSONAL.XLSB so
should I type that in in order to esnure the macro doesnt randomly close it?

Regards
Murray

 

[MurrayB]

Thanks again Dave and I hear you re the auto save/close issues. I only need
this macro for a small amount of work that I do where I have 30 odd
spreadsheets open at a time that are all linked so I need to save them all
in order to save all the link updates anyway.

 

[MurrayB]

One more thing Dave - the Macro below gives me an error 1004 if Excel cannot
save the document and then asks to Debug or End the macro. Could you help me
with some code to manually intervene in that case?

 

[Dave Peterson]

I would remove all doubt by using lcase(), ucase() or strcomp().

I surely wouldn't trust my memory of how I typed the name!

 

[Dave Peterson]

I'm not sure what version you used, but...

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
On Error Resume Next
w.Close savechanges:=True 'false?
If Err.Number <> 0 Then
MsgBox w.Name & " wasn't saved!"
Err.Clear
End If
On Error GoTo 0
End If
Next w

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

End Sub

You could add the same error checking to the "thisworkbook.close..." line, too.

One more thing Dave - the Macro below gives me an error 1004 if Excel cannot
save the document and then asks to Debug or End the macro. Could you help me
with some code to manually intervene in that case?

<<snipped>>