Microsoft KB Archive/76442

= Macro to Save All Open Worksheets in Excel =

Article ID: 76442

Article Last Modified on 7/30/2001



This article was previously published under Q76442



SUMMARY
The following explains a macro that adds a Save All command to the File menu in Microsoft Excel. When selected, all files that have been saved previously will be saved automatically. If files are open that have not been saved previously, a dialog box will appear, prompting the user for a filename.



MORE INFORMATION
If you are using Excel version 3.0, you can use the add-in macro AUTOSAVE.XLA to save all open documents automatically as you work. This add-in macro is found in the EXCEL\LIBRARY subdirectory and is documented in the &quot;Microsoft Excel User's Guide.&quot;

Enter the following information into a macro sheet:   A1: sav_menu A2: =ADD.COMMAND(1,&quot;file&quot;,A10:C10,7) A3: =ADD.COMMAND(2,&quot;file&quot;,A10:C10,7) A4: =ADD.COMMAND(4,&quot;file&quot;,A10:C10,7) A5: =ADD.COMMAND(5,&quot;file&quot;,A10:C10,7) A6: =ADD.COMMAND(6,&quot;file&quot;,A10:C10,7) A7: =RETURN A8: A9: A10: Sa&ve All         B10: Save   C10: Save all open documents A11: A12: Save A13: =ECHO(FALSE) A14: =FOR(&quot;i&quot;,1,COLUMNS(WINDOWS),1) A15: =ACTIVATE(INDEX(WINDOWS,1,i)) A16: =GET.DOCUMENT(2) A17: =IF(ISERROR(B16),SAVE.AS?,SAVE) A18: =NEXT A19: =MESSAGE(TRUE,&quot;All files have been saved!&quot;) A20: =ACTIVATE.PREV A21: =WAIT(NOW+0.00003) A22: =MESSAGE(FALSE,&quot;&quot;) A23: =RETURN After entering the above information, define the two macros shown above. To do this:


 * 1) Select cell A1.
 * 2) From the Formula menu, choose Define Name. Select the Command option and choose the OK button.
 * 3) Select cell A12 and repeat step 2 above.

If you would like the Save All command to appear in the File menu every time you open this particular macro sheet, you must define an Auto_Open macro. You can do this by following steps 1 and 2 above, except that in step 2 you must type Auto_Open in the Name text box before choosing the OK button.

For information about how to do this with a Visual Basic, Applications Edition, macro, please see the following article(s) in the Microsoft Knowledge Base:

131333 &quot;Visual Basic Macro to Save All Open Workbooks in MS Excel&quot;

