Auto Save and Close all open files

[julhs]

This should be as simple simple as can be BUT!!
I have 2 workbooks open; I’m in Book 1 making it the “Active Book”
In Book1 I have testing module that when run is supposed to save and close ALL workbooks.

Sub Close_All_Files_Save()
'Close all open workbooks and save
Dim wb As Workbook
'Loop through each workbook
For Each wb In Application.Workbooks
'Close the workbooks and save changes
wb.Close SaveChanges:=True
Next wb
End Sub

However, it ONLY saves and closes Book1 (the Active Workbook) BUT not Book2 as well.
I have also tested this (run from Book1)

Sub Close_All_Files_Save()
'Close all open workbooks and save
Dim wb As Workbook
'Loop through each workbook
For Each wb In Application.Workbooks
'Prevent the workbook that contains the code from being closed
If wb.Name <> ThisWorkbook.Name Then
'Close the workbook and save changes
wb.Close SaveChanges:=True
End If
Next wb
End Sub

It closes and saves Book2 and leaves Book1 open (as it is coded to do)

Question is WHY wouldn’t the first code; to save and close BOTH books fully work?
Help as ever is greatly appreciated!!

 

[rlv01]

Likely because it closes the workbook running the code before the other ones. You have to leave that workbooks for last.

Try something like this.

Sub Close_All_Files_Save()
    'Close all open workbooks and save
    Dim wb As Workbook
   
    'Loop through each workbook
    For Each wb In Application.Workbooks
       
        'Close the workbooks and save changes, except this one.
        If wb.Name <> ThisWorkbook.Name Then
            wb.Close SaveChanges:=True
            DoEvents
        End If
    Next wb
   
    ThisWorkbook.Save
    ThisWorkbook.Close SaveChanges:=False
End Sub

 

[julhs]

Thank you for that.
I had tried changing the order of closure in the first code without success so never even attempted to do it in the second one.
I had to chuckle when I ran your suggestion; YES both open workbooks saved and close, but an instance of Excel remained open showing the forums “Xl2bb” Addin.
I added "Application.Quit" at the end of your code but that didn't exit Excel completly, it still left an Excel window open showing just the “Xl2bb” Addin.
Any suggestions?

 

[rlv01]

Delete this line ThisWorkbook.Close SaveChanges:=False and replace it with this: Application.Quit

Sub Close_All_Files_Save()
    'Close all open workbooks and save
    Dim wb As Workbook
   
    'Loop through each workbook
    For Each wb In Application.Workbooks
       
        'Close the workbooks and save changes, except this one.
        If wb.Name <> ThisWorkbook.Name Then
            wb.Close SaveChanges:=True
            DoEvents
        End If
    Next wb
   
    ThisWorkbook.Save
    Application.Quit
End Sub

 

[julhs]

This is now a bit of an academic question really; because using your modified code solved things.
Have to admit that the below part confused me (not difficult to do)

ThisWorkbook.Save
ThisWorkbook.Close SaveChanges:=False

I would have expected it to read “=True”; question is, is it set to “False” due to the use of “DoEvents” or what?
For heaven’s sake, if my supplementary question is too convoluted to answer in a concise way please don’t bother.
More than great full for a solution to posted question.

 

[rlv01]

I would have expected it to read “=True”; question is, is it set to “False” due to the use of “DoEvents” or what?

File operations like save take time. Because the preceding statement ThisWorkbook.Save already saved the workbook, there is no reason to save it again during the close statement.
ThisWorkbook.Close SaveChanges:=False is faster than ThisWorkbook.Close SaveChanges:=True

 

[julhs]

Sorry to come back to you on this after all this time.
Moderators will soon tell me if my follow up question should be a new post!

I hadn’t thought through how I was ACTUALLY going to run the code.
Option one; assign “Sub Close_All_Files_Save” code that resides in a standard module to a command button on the primary workbook active sheet. This works, all workbooks are saved and closed. (Also note; just running the sub from the standard module everything saves and closes.)
Option two; in “ThisWorkbook” module of the primary workbook use “Sub Workbook_BeforeClose(Cancel As Boolean) that calls “Sub Close_All_Files_Save”
But trying to do things this way it is leaving a BLANK instance of Excel still open, no matter what I have tested I can’t prevent it from appearing by code, have to resort to closing it down manually.
Have you any suggestions?

 

[rlv01]

If I understand correctly, you are saying that option one works, while option two does not. Picking which option to use seems like an easy descision.

 

[julhs]

Yes that’s correct, and I take your point.
But by all accounts I can use “Workbook_BeforeClose” in the primary’s workbook “ThisWorkbook” to call “Sub Close_All_Files_Save”.
What I’m not understanding is why if “Sub Close_All_Files_Save” is run from a command button, ALL instances of Excel are closed.
But using “Workbook_BeforeClose” procedure in primary’s workbook “ThisWorkbook” to call the exact same sub it’s leaving this blank instance of Excel still open.
I don’t want to exhaust your patience with me on this, but logic says it should work 100% regardless of how it is implemented?

 

[rlv01]

Using the VBE, set a breakpoint on the first line of code in Workbook_BeforeClose, then single-step from that point through the entire save process and see what you learn.