Excel Macro: Saving and Closing All Workbooks

This Excel VBA macro allows us to save and close all workbooks.

Macro Example

Sub SaveAndCloseAllWorkbooks()

  Dim bk As Workbook
 
  For Each bk In Workbooks
    If Not bk Is ThisWorkbook Then
      bk.Close SaveChanges:=True
    End If
  Next bk
 
  ThisWorkbook.Close SaveChanges:=True

End Sub

Description

Variable Declaration

In the 3rd line, We first declare an object variable named bk represents a Workbook object.

Sub...

  Dim bk As Workbook

The Workbook object represents a single Excel workbook.

obj-Excel-Workbook

Starts Looping

The 5th line starts to loop through all the elements (=Workbook) of a collection (=Workbooks).

Sub...

  Dim bk As Workbook

  For Each bk In Workbooks

The Application.Workbooks property returns the Workbooks collection object.

obj-Excel-Appliication-Workbooks

The Workbooks property is a member of the Excel.Global class, so We can omit the Application property.

obj-Excel-Global-Workbooks

The Workbooks object is a collection of all the Workbook object opened in Excel.

obj-Excel-Workbooks

Is this ThisWorkbook?

The 6th line checks whether the bk is this workbook.
If this workbook will be closed, this macro has to be stopped. So, we have to use this If statement.

Sub...

  Dim bk As Workbook

  For Each bk In Workbooks
    If Not bk Is ThisWorkbook Then

The ThisWorkbook property returns the Workbook object that represents the workbook where the current macro code is running.

obj-Excel-ThisWorkbook

The ThisWorkbooks property is also a member of the Excel.Global class, so we can omit the Application property.

Saving and Closing

The 7th line saves and closes the workbook by the Workbook.Close method.

Sub...

  Dim bk As Workbook

  For Each bk In Workbooks
    If Not bk Is ThisWorkbook Then
      bk.Close SaveChanges:=True
    End If
  Next bk

obj-Excel-Workbook-Close

By setting the SaveChanges parameter to True, the workbook is saved.
If there is not yet a file name associated with the workbook, we are asked to supply a file name.

Save and Close ThisWorkbook

The 11th line saves and closes this workbook.

Sub...

  Dim bk As Workbook
 
  For Each bk In Workbooks
    If Not bk Is ThisWorkbook Then
      bk.Close SaveChanges:=True
    End If
  Next bk

  ThisWorkbook.Close SaveChanges:=True

After this line is evaluated, the macro ends.

Sub SaveAndCloseAllWorkbooks()

  Dim bk As Workbook
 
  For Each bk In Workbooks
    If Not bk Is ThisWorkbook Then
      bk.Close SaveChanges:=True
    End If
  Next bk
 
  ThisWorkbook.Close SaveChanges:=True

End Sub

Properties

http://www.relief.jp/itnote/archives/017811.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007