VBA to close workbooks

blatham

New Member
Joined
Nov 8, 2005
Messages
47
Hi

Can anyone help me with the code needed to loop through all open workbooks and close the ones that are hodden (apart from Personal.xls).

Regards

Ben
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Code:
application.displayalerts = false

for each item in workbooks

    item.close

end if

application.displayalerts = true

Is one method... it only closes visible workbooks, and saves each one first.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Sub Test()
    Dim Win As Window
    For Each Win In Application.Windows
        If Win.Visible = False And UCase(Win.Parent.Name) <> "PERSONAL.XLS" Then
            Win.Parent.Close
        End If
    Next Win
End Sub
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
444

ADVERTISEMENT

In the excel help file I found:

Example
This example closes Book1.xls and discards any changes that have been made to it.

Workbooks("BOOK1.XLS").Close SaveChanges:=False

This example closes all open workbooks. If there are changes in any open workbook, Microsoft Excel displays the appropriate prompts and dialog boxes for saving changes.

Workbooks.Close

But while developing some code, I need to close all the workbooks and don't want to mess with that pesky message about changing them so I tried:

Code:
Workbooks.Close SaveChanges:=False

Excel tells me wrong number of arguments or invalid property assignment.

What!!!! If I can close one, I darn sure ought to be able to close all. How can I make this one liner work?

BTW: I used forum search to find
workbooks.close
and the quoted string
"workbooks.close"
and got some 8000 finds. All the responses that I looked at had either workbooks or close, but none had the phrase "workbooks.close"

How do I accomplish this search?
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Workbooks is a collection, so you cannot simply say Workbooks.Close. To use the Close method, you need to identify exactly which workbook you want to close. If you notice in the example from the help file, they are closing Book1: Workbooks("BOOK1.XLS").Close. In my example, I am parsing the entire workbook collection... after seeing Andrew's code, I realized that mine needed some refining (that's what i get for posting code w/o trying it out) as it will in fact close all open workbooks.

To parse an entire collection (Windows, Workbooks, whatever) use the For Each Item In..Next, as demonstrated in both my and Andrew's code. Also note that use of application.displayalerts = false will prevent the message asking you want to save changes...
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
444
Hello hatman,
Thanks for your response.

I am developing some code that opens multiple workbooks and moves data between them. At the end of my code was the line:

Workbooks.Close

It did close them all. However, due to the mere act of opening workbooks, and the fact that I made changes, caused Excel to prompt me if I wanted to save the changes. In this case I did not want to save anything, I was testing code and saving the results of my mistakes would cause serious problems. I wanted to just close all of them without changes and be done with it.

While writing this it ocuured to me, set the files to read only, suppress alerts, and maybe that would work. Just the same, there should be a way to do this deed directly. (I.e. some line of code that effectively says: "Excel, Close all of this crap, don't save anything, and don't prompt for anything.)
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Hello hatman,
Thanks for your response.

I am developing some code that opens multiple workbooks and moves data between them. At the end of my code was the line:

Workbooks.Close

It did close them all.
Well I learned something :eek: . I did not think that it would work like that... though I would speculat ethat the parameters for closing an entire collection woul dbe different from the parameters to close a single workbook... hence the error.

However, due to the mere act of opening workbooks, and the fact that I made changes, caused Excel to prompt me if I wanted to save the changes. In this case I did not want to save anything, I was testing code and saving the results of my mistakes would cause serious problems. I wanted to just close all of them without changes and be done with it.

While writing this it ocuured to me, set the files to read only, suppress alerts, and maybe that would work. Just the same, there should be a way to do this deed directly. (I.e. some line of code that effectively says: "Excel, Close all of this crap, don't save anything, and don't prompt for anything.)

Suppressing alerts actually causes Excel to select the Yes Okay or any other positive choice that is presented in dialogs. So this would not be appropriate in your case. I don't know that you can do this wholesale the way that you want. Seems to me you have already hit upon once option: open them Read Only... the other is to parse the collection one workbook at a time and use the savechanges parameter for each workbook individually...
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,335
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top