If you have a number of linked Excel files and you make a structural change (insert rows or columns) you need to save all the open files to ensure that the links are updated and retained. This can be tedious as there is no Save All option in Excel. Word has a Save All option, but not Excel.
A macro can fill this gap in Excel’s functionality.
The macro below will save all the open Excel files except any that are read only or any that haven’t been already saved eg new files that have just been created and not saved.
Sub SaveAllOpenFiles()
' saves all open Excel files
' excludes
' read only +
' previously unsaved files
Dim wb As Workbook
'go through all open workbooks
For Each wb In Workbooks
'only save those that
'aren't read only +
'those not previously saved (new workbooks)
'.Path is the folder path of the file
If Not wb.ReadOnly And Len(wb.Path) <> 0 Then
wb.Save
End If
Next wb
'clears the object variable from memory
Set wb = Nothing
End Sub
The macro goes through all the open workbooks checks their status and then saves those that meet the criteria.
To see instructions on how to install and use macros see this blog post.
Click here to see how you can run a Macro off a Quick Access Toolbar icon.
Did not work for me in the 2010 64 bit version of Excel.
Sorry, the If statement is not displaying correctly. It should be
If Not wb.ReadOnly And Len(wb.Path) > 0 Then
I am working on getting it fixed in the programming display
I just wanted to say thank you so much, I found two other sites with incorrect, non-working code for this task, but your code (once I made the change in the Comments) worked. Can’t wait to set this up on my work computer and then on my boss’s work computer!
Hi Eva – happy to help.
I tweaked the code in html so it displays ok now.