Excel Macro: Saving All Workbooks and Quit

I’ve described an Excel VBA macro to save and close all workbooks.
Today, I’ll show you a similar macro which allows us to save all workbooks and to quit Excel.

Macro Example

Sub SaveAllWorkbooksAndQuit()

  Dim bk As Workbook
 
  For Each bk In Workbooks
    bk.Save
  Next bk
 
  Application.Quit

End Sub

Description

Variable Declaration

The 3rd line declares an object variable named bk that refers to 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 a Workbooks collection object.

obj-Excel-Appliication-Workbooks

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

obj-Excel-Workbooks

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

obj-Excel-Global-Workbooks

Saving

The 6th line saves an individual workbook by the Workbook.Save method.

Sub...

  Dim bk As Workbook

  For Each bk In Workbooks
    bk.Save

obj-Excel-Workbook-Save

Loops Back

The 7th line loops back to get the next workbook.

Sub...

  Dim bk As Workbook

  For Each bk In Workbooks
    bk.Save
  Next bk

Application.Quit

The 9th line quits excel by the Application.Quit method.

Sub...

  bk As Workbook
 
  For Each bk In Workbooks
    bk.Save
  Next bk
 
  Application.Quit

The Application property returns the Application object.

obj-Excel-Global-Appliication

And, the Application.Quit method quits Excel.

obj-Excel-Appliication-Quit

After this line evaluated the macro ends.

Sub SaveAllWorkbooksAndQuit()

  Dim bk As Workbook
 
  For Each bk In Workbooks
    bk.Save
  Next bk
 
  Application.Quit

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007