XL: Visual Basic Macro to Save All Open Workbooks in MS Excel

ID: Q131333

The information in this article applies to:

SUMMARY

The following is an example of a Microsoft Visual Basic for Applications macro that automatically saves all files that have been previously saved. If files are open that have not been previously saved, a dialog box will prompt the user for a file name.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/
In Microsoft Excel, you can use the AUTOSAVE.XLA add-in macro to save all open documents automatically as you work. If you prefer not to have your documents automatically saved, but would like to save all open documents at

one time, use the following sample Visual Basic for Applications macro: 

   Sub SaveAll()
       ' Store the Activeworkbook in a variable.
       Set aw = ActiveWorkbook
           For Each wb In Workbooks
               If wb.Path <> "" Then ' Save file if it has been saved
                                     ' previously
                   wb.Save
               Else  ' If not previously saved, activate and show the
                     ' Save As dialog box.
                   wb.Activate
                   Application.Dialogs(xlDialogSaveAs).Show
               End If
           Next
       aw.Activate   ' Activate the original Activeworkbook.
   End Sub
After you create the macro, do the following to create a menu item and assign the macro to it:

Microsoft Excel 5.0 or 7.0

1. If the Visual Basic Toolbar is not showing, click Toolbars on the View 

   menu. In the Toolbars dialog box, click Visual Basic in the list of
   Toolbars, and click OK.
2. Make sure the workbook that contains the macro is active, and click the 
   Menu Editor button on the Visual Basic Toolbar.
3. In the Menu Editor dialog box, select &File under Menus, select Save 
   &Workspace in the Menu Items list, and click the Insert button.
4. In the Caption box, type Save &All (without the quotation marks).

5. In the Macro list, choose the SaveAll macro.

6. To add an entry to menus other than the Worksheet menu, select the 

   appropriate menu bar from the Menu Bars list and repeat the steps 3-5.
7. Once you have made all the desired changes, click OK.

Microsoft Excel 97 or Microsoft Excel 98

1. On the View menu, point to Toolbars, and then click Customize. Click 

   the Commands tab.
2. In the list of Categories, click Macros.

3. Drag the item "Custom Menu Item" from the Commands list box to the File 

   menu.
4. Right-click (or CONTROL-click) the new menu item. In the Name box on 
   the shortcut menu, type "Save &All" (without the quotation marks).
   Click Assign Macro on the shortcut menu.
5. In the list of macros, select the SaveAll macro, and then click OK.

6. In the Customize dialog box, click Close.

To run the macro and save all open workbooks, click Save All on the File menu. You can create this macro and make the menu changes in a new workbook

or in an existing workbook. To make the menu choice always available in Microsoft Excel, store the macro and make the menu changes in the Personal Macro Workbook.

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 12, "Creating Menuing Items"

For more information about using the Menu Editor, click the Search button in Microsoft Excel Help and type: 

   Menu Editor
For additional information, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q76442
   TITLE     : Macro to Save All Open Worksheets in Excel
Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL5 XL7 XL97 XL98

menubar 

Keywords          : kbprg kbdta kbdtacode PgmHowto KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 18, 1999