List Open Unsaved Workbooks in Excel VBA

List Open Unsaved Workbooks in Excel VBA. It helps to know all open and unsaved workbooks name. It is easy shift between unsaved workbooks. We have an option to open multiple workbooks in MS Office Excel. Let us see in the following tutorial how to list all open unsaved Workbook names in Excel VBA.

Table of Contents:

Objective
Syntax to VBA Loop through all Workbooks in Excel
Macro to VBA List Open Unsaved Workbooks in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax to VBA Loop through all Workbooks in Excel

Here is the following syntax to loop through all Workbooks in Excel VBA.

For Each Workbook in Application.Workbooks
      Statements...
Next

Where Workbook: It represents Workbook object which is part of workbooks collection.

Macro to List Open Unsaved Workbooks in Excel VBA

Let us see the following example macro to List all Open unsaved Workbooks in Excel VBA.

'VBA List All Open Unsaved Workbooks in Excel
Sub VBA_List_All_Open_Unsaved_Workbooks()
    
    'Variable declaration
    Dim xWorkbook As Workbook
        
    'Loop through all workbooks
    For Each xWorkbook In Application.Workbooks
        
        'Check Workbook is current Workbook or not
        If xWorkbook.Name <> ThisWorkbook.Name Then
            'Check if the file names has an extension
            
            If InStr(Right(xWorkbook.Name, 5), ".xl") > 0 Then
                'Save and Close Workbook
                xWorkbook.Close SaveChanges:=True
            Else
                Debug.Print xWorkbook.Name
            End If
        End If
    Next

End Sub