Which Excel files in folder are password protected?

This article explains how to check if Excel files in a given folder are password protected. The image above shows the folder path in cell C4 that the macro will use.

Press with left mouse button on the button "Select a folder" to run the macro, a dialog box asks for the folder path. The folder path will be shown in cell C4 and the result will be displayed in cell range B6:C7 and cells below.

As far as I know, you can't check if an Excel file is password protected without opening the file. The macro opens workbooks in a folder, check if password protected and then close the workbook. One by one.

This technique can be slow if you have many Excel files saved in the folder, however, it is much faster than doing this manually.

VBA Code

'Name macro
Sub CheckWbook()

'Dimension variables and declare data types
Dim Value As String, a As Single

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Application.FileDialog(msoFileDialogFolderPicker)

    'Ask for a folder path
    .Show

    'Save folder path to variable myfolder
    myfolder = .SelectedItems(1) & "\"
End With

'Save folder path to cell C4
Range("C4") = myfolder

'Clear cell range B7:C1048576
Range("B7:C" & Rows.Count) = ""

'Save 0 (zero) to variable a
a = 0

'Dir function returns a string representing the name of a file or folder that matches a specified pattern.
Value = Dir(myfolder)

'Keep iterating while variable Value is not empty
Do Until Value = ""

    'Check if variable Value is equal to "." or ".."
    If Value = "." Or Value = ".." Then

    'Continue here if variable Value is NOT equal to "." or ".."
    Else

        'Check if file extension is xls, xlsx or xlsm
        If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then

            'Enable error handling
            On Error Resume Next

            'Open workbook based on variable myfolder and Value using password "zzzzzzzzzzzz"
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"

            'If an error is returned write "Yes" to cell in column C based on variable a
            If Err.Number > 0 Then
                Range("C7").Offset(a, 0).Value = "Yes"
            End If

            'Close workbook without saving any changes
            Workbooks(Value).Close False

            'Disable error handling
            On Error GoTo 0

            ' Save file name to cell in column B based on variable a
            Range("B7").Offset(a, 0).Value = Value

            'Add 1 to variable a
            a = a + 1
        End If
    End If

    'Continue with next file or folder
    Value = Dir

'Go back to "Do Until"
Loop

End Sub

Where to put the code?

1. Copy VBA code. (CTRL + c)
2. Press shortcut keys Alt + F11 to open the Visual Basic Editor.
3. Doublepress with left mouse button on your workbook in the "Project Explorer" window.
4. Press with left mouse button on "Insert" on the top menu.
5. Press with left mouse button on "Module" to insert a module to current workbook.
6. Paste code to module, see image above. CTRL + v.

Get the Excel file

List-password-protected-workbooks.xlsm

List password-protected workbooks and worksheets in folder

The following VBA macro creates a list of workbooks and worksheets located in a given folder. It also returns "Yes" if a workbook or a worksheet is password-protected.

The button named "Select a folder" displayed in the image above allows you to run the macro, press with left mouse button on the button to start the macro. The macro then asks for a folder to work with, note that it won't continue with sub-directories for that particular folder.

VBA Code

'Name macro
Sub PassWordWorkbook()

'Dimension variables and declare data types
Dim Value As String, a As Single

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property
With Application.FileDialog(msoFileDialogFolderPicker)
    
    'Ask for a folder path
    .Show

    'Save folder path to variable myfolder
    myfolder = .SelectedItems(1) & "\"
End With

'Save a reference of current workbook to object ws 
Set ws = ActiveSheet

'Save folder path to cell C4
Range("C4") = myfolder

'Clear cell range B7:C1048576
Range("B7:C" & Rows.Count) = ""
'Save 0 (zero) to variable a
a = 0

'Dir function returns a string representing the name of a file or folder that matches a specified pattern.
Value = Dir(myfolder)

'Keep iterating while variable Value is not empty
Do Until Value = ""

    'Check if variable Value is equal to "." or ".."
    If Value = "." Or Value = ".." Then
    Else

        'Continue here if variable Value is NOT equal to "." or ".."
        If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
            
            'Enable error handling
            On Error Resume Next

            'Open workbook based on variable myfolder and Value using password "zzzzzzzzzzzz"
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"

            'If an error is has occured write "Yes" to cell in column C based on variable a
            If Err.Number > 0 Then
                ws.Range("D7").Offset(a, 0).Value = "Yes"
            End If

            'Disable error handling
            On Error GoTo 0

            ' Save file name to cell in column B based on variable a
            ws.Range("B7").Offset(a, 0).Value = Value

            'Add 1 to variable a
            a = a + 1

            'Enable error handling
            On Error Resume Next

            'Iterate through each worksheet in current workbook
            For Each sht In ActiveWorkbook.Worksheets

                'Save worksheet name to column C based on variable a
                ws.Range("C7").Offset(a, 0).Value = sht.Name

                'Check if worksheet is password protected
                If sht.ProtectContents Then

                    'Unprotect worksheet
                    sht.Unprotect ""

                        'Check if an error has occured
                        If Err.Number > 0 Then

                            'Save "Yes" to column D based on variable a
                            ws.Range("D7").Offset(a, 0).Value = "Yes"
                        End If
                End If

                'Add 1 to variable a
                a = a + 1

            'Continue with next worksheet
            Next sht

            'Disable error handling
            On Error GoTo 0

            'Close workbook
            Workbooks(Value).Close False
        End If
    End If

    'Continue with next file or folder
    Value = Dir

'Go back to "Do Until"
Loop

End Sub

Where to put the code?

Files and folders category

Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Search for a file in folder and subfolders [UDF]
Table of Contents Search for a file in folder and sub folders - User Defined Function Search for a file […]

Excel categories

Comments (6)