Excel - close and save inactive workbooks

[srogers]

In the process of running some reports the user may end up with 3 workbooks open.

I'm saving my active workbook fine and closing it with application.quit.

Sometimes the other files close if there were no changes, but if there were any changes a dialog box opens asking them if they want to save the file.
They have requested that the other files save and close also. Can I do this?

One of the files has a static name. The other file name changes each month (with a month number). I think I can capture that number out of the file if I need to.

I tried this:
Workbooks("MyFile.XLS").Close SaveChanges:=True
Application.Quit

But it closed & didn't save.
Thank you -

 

[Loomah]

Hi
There's no reason I can think of why the method you're using won't work. You will, however, need to reference each workbook you are closing. If your files have the same name apart from the month you could use something like

workbooks("myfile" & month(now)) - current month

to reference the names. alternatively you could assign the workbooks to variables when you open if you are oopening them programmatically.

I think I need alittle more info to work with here. And it's now past my bed time!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Bowers74]

The following procedure will close all open workbooks except for the active workbook. If the workbook has already been saved (i.e. has a *.xls extention), it will be saved. If the workbook has never been saved (i.e. NO *.xls extention), the user will be asked if they wish to save it or not with the typical warning message.

Sub CloseAndSaveInactiveWorkbooks()
Dim wb As Workbook
For Each wb In Application.Workbooks
    If Not Not wb.Name <> ActiveWorkbook.Name Then
        If wb.Name Like "*.xls" Then
            wb.Close SaveChanges:=True
        Else
            wb.Close
        End If
    End If
Next wb
End Sub

I hope this helps!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884