Home > Macros > Basics > List Files

List and Close All Excel Files

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.

worksheet buttons

Excel Workspace

Did you ever use Excel's Workspace feature, that disappeared in Excel 2013?

With Workspace, you could open two or more workbooks, arrange them in the Excel window, and save that setup with a Workspace name.
Later, you could open that Workspace, and quickly get back to work. All of the files opened automatically, and were in the same layout that you created earlier.

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.

Click a button, and a macro lists all the open files, and closes each file.
To get back to work, open the macro file, and click a button to open all the listed files.

Workbook Setup

In the sample file for this workbook, the FileList sheet has 2 buttons that run macros:

List and Close Workbooks - creates a list of all the open Excel files
Open All Workbooks in List - opens all the Excel files listed on the worksheet

worksheet buttons

Workbook List Tips

Here are a couple of tips for working with the List and Close Workbooks sample file.

After the list is created, you can sort it alphabetically, to make it easier to find files in a long list
Before clicking the Open All Workbooks button, check the file list.
- If you don't want to open one of the listed files, select that cell and clear the contents

worksheet buttons

Workbook List Macros

There are 2 macros in the sample file:

ListAndCloseWorkbooks - runs when you click the List and Close Workbooks button
OpenAllListedWorkbooks - runs when you click the Open All Workbooks button

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:

Right-click on the worksheet button
Click on Assign Macro
In the Assign Macro window, click the Edit button

worksheet buttons

List and Close Macro

Here is the code for the ListAndCloseWorkbooks macro that creates a list of all the open Excel files, on the FileList sheet.

The macro clears column 3, to remove any files names that were previously listed there.
Then, the macro checks each Excel file that is open
If the file is an add-in, its name is not listed on the worksheet. Add-in files are hidden, and they open automatically when Excel starts.
If the file is NOT an add-in, its file path and name are listed on the worksheet, except for the workbook that contains the ListAndCloseWorkbooks macro.
If no files are listed on the worksheet, a message appears - "No other workbooks are open."

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

Open All Workbooks Macro

Here is the OpenAllListedWorkbooks macro that opens each of the Excel files from the list on the FileList sheet.

The macro finds the row number of the last filled cell in column C
Starting in cell C1, and down to the last filled cell, it opens the file that is listed in the cell
If a cell in the list is empty, that cell is skipped

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

Download the Sample File

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.

List and Close All Excel Files

Excel Workspace

Workbook Setup

Workbook List Tips

Workbook List Macros

List and Close Macro

Open All Workbooks Macro

Download the Sample File

More Tutorials