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

ID: Q131333

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

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/overview/overview.asp

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

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.

Make sure the workbook that contains the macro is active, and click the Menu Editor button on the Visual Basic Toolbar.

In the Menu Editor dialog box, select &File under Menus, select Save &Workspace in the Menu Items list, and click the Insert button.

In the Caption box, type Save &All.

In the Macro list, choose the SaveAll macro.

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.

Once you have made all the desired changes, click OK.

Microsoft Excel 97 or Microsoft Excel 98

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

In the list of Categories, click Macros.

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

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.

In the list of macros, select the SaveAll macro, and then click OK.

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:

Q76442 Macro to Save All Open Worksheets in Excel

Additional query words: 5.00a 5.00c 7.00a 97 98 XL5 XL7 XL97 XL98 menubar

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