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

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for 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 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 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/default.asp

   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

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.

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

   ARTICLE-ID: Q76442
   TITLE     : Macro to Save All Open Worksheets in Excel