VBA to close workbooks

[blatham]

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

 

[hatman]

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]

Try:

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

 

[blatham]

That's great. Thanks for your help.

 

[bkelly]

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:

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]

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]

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]

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