Visual Basic Macro to Save All Open Workbooks in MS Excel

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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.
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. 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.