Sunday, March 02, 2008

Save and Reopen all Workbooks (Excel VBA)

Save All Open Workbooks and Re-open them using VBA

Use SaveWorkspace method to save the current workspace and use the Open method to open the files again

Sub WorkPlace_Save_And_Resume()

' Save Workspace

Application.DisplayAlerts = False

Application.SaveWorkspace "C:\New Folder\TempWorkSpace.XLW"


' Do my work here without anyother workbooks

Workbooks.Open "C:\New Folder\TempWorkSpace.XLW"

Application.DisplayAlerts = True

End Sub

The files in the application will be saved and re-opened using Open method

See also :

Excel VBA - 1004 -- The file could not be accessed

Save and Reopen all Workbooks (Excel VBA)

Save copy of the workbook

SaveAs Dialog - Controlled Save

Save RTF document as word


  1. Hi. This is exactly what I want to do. Did I understand something wrong?

    This code will not work for workbooks within the same workbook.
    Execution ends at the "Workbooks.Close" statment.

  2. Workbooks.Close will close all open workbooks in that particular Excel instance.

    If you are using this command from an Open Workbook, it will close that too. If the workbook is not saved, it might prompt for confirmation

    If the DisplayAlerts is set to false, you can avoid the prompt

    Application.DisplayAlerts = False


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.