Code to close many open workbooks
 

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

Code to close many open workbooks
 

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

Code to close many open workbooks
 

On Sep 16, 3:22*pm, "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

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)

Code to close many open workbooks
 

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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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" wrote in message
...
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

Code to close many open workbooks
 

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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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" wrote in message
...
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

"MurrayB" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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" wrote in message
...
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

"MurrayB" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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" wrote in message
...
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

"MurrayB" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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

"Dave Peterson" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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.


"Dave Peterson" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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

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

MurrayB wrote:

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

"Dave Peterson" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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" wrote in message
...
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

"MurrayB" wrote in message
...
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

Code to close many open workbooks
 

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.

MurrayB wrote:

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

Dave Peterson