How can I save all open excel files?


C

Cobaum

Is there a way to save all open Excel files with one command? I am working
on annual budget and have ~70 open files and I toggle back and forth so often
I forget what I saved. Is there one command that will save them all at the
same time? I can click on the red close-excel "X" at the top and click yes
to all, but I do not want to close them. Suggestions? Thanks in advance.
 
Ad

Advertisements

D

Dave Peterson

You could use a macro:

Option Explicit
Sub SaveThemAll()
Dim wkbk As Workbook
Dim okCtr As Long

okCtr = 0
For Each wkbk In Application.Workbooks
If wkbk.Path = "" Then
'hasn't been saved, so skip it
Else
If wkbk.Saved = True Then
'skip it, why bother
Else
On Error Resume Next
wkbk.Save
If Err.Number <> 0 Then
MsgBox "Error saving: " & wkbk.FullName _
& vbLf & Err.Number & vbLf & Err.Description
Err.Clear
Else
Beep 'some indicator
Application.StatusBar = "Saved: " & wkbk.Name & " at " & Now
okCtr = okCtr + 1
End If
On Error GoTo 0
End If
End If
Next wkbk

Application.StatusBar = False

MsgBox okCtr & " of " & Application.Workbooks.Count & " saved."

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
D

Don Guillett

Here's one I use to save and close all and leave Excel

Sub CLOSE_ALL()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In Application.Workbooks
w.Save
Next w
application.Quit
End Sub
 
Ad

Advertisements

C

Cobaum

So Excel never put in thier program? I am surprized. I would have thought
that there would have been a command...
Thanks for the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top