At end of your work day, use this macro to list and close all open Excel files. The next day, click a worksheet button, and a macro opens those files again. A workaround for Excel's old Workspace feature.
Did you ever use Excel's Workspace feature, that disappeared in Excel 2013?
If you miss that old Excel workspace feature, this sample file and its macros might help you. It's not exactly the same, but it helps you open several files, and get back to working with them.
In the sample file for this workbook, the FileList sheet has 2 buttons that run macros:
Here are a couple of tips for working with the List and Close Workbooks sample file.
There are 2 macros in the sample file:
NOTE: You don't have to look at the macro code, unless you're interested in seeing how it works, or making minor changes to it.
In the sample file, if you want to see the macro code for either of the worksheet buttons:
Here is the code for the ListAndCloseWorkbooks macro that creates a list of all the open Excel files, on the FileList sheet.
Sub ListAndCloseWorkbooks() Dim wbA As Workbook Dim wbO As Workbook Dim ws As Worksheet Dim RowNum As Long Dim ListCol As Long ListCol = 3 Set wbA = ActiveWorkbook Set ws = ActiveSheet With ws 'clear the old list .Columns(ListCol) _ .EntireColumn.ClearContents 'list currently open files 'does not list this workbook For Each wbO In Application.Workbooks If wbO.IsAddin = False Then If wbO.Name <> wbA.Name Then RowNum = RowNum + 1 ws.Cells(RowNum, ListCol).Value _ = wbO.FullName wbO.Close End If End If Next wbO End With If RowNum = 0 Then MsgBox "No other workbooks are open" End If End Sub
Here is the OpenAllListedWorkbooks macro that opens each of the Excel files from the list on the FileList sheet.
TIP: If you don't want to open one of the listed files, select that cell and clear the contents
Sub OpenAllListedWorkbooks() Dim ws As Worksheet Dim MyFile As String Dim i As Long Dim LastRow As Long Dim ListCol As Long Set ws = ActiveSheet ListCol = 3 'open all workbooks listed in column C With ws LastRow = .Cells(.Rows.Count, ListCol) _ .End(xlUp).Row For i = 1 To LastRow MyFile = "" MyFile = .Cells(i, ListCol).Value If MyFile <> "" Then Workbooks.Open (MyFile) End If Next i End With End Sub
To see the sample file from this tutorial, download the List and Close All Excel Files workbook. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.
Last updated: October 7, 2020 3:57 PM