Author: Oscar Cronquist Article last updated on March 30, 2020

Are Excel files in folder 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.

Click 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.

rahul jadhav asks: How can we identify any protected excel file before opening using macro VBA (e.g. i have a folder with multiple excel files i need to find out using macro how many files are password protected before opening and it should give result on separate workbook.)
is it possible using excel vba macro?

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?

Which Excel files in folder are password protected 1

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

List password-protected workbooks and worksheets in folder

Are Excel files and worksheets in folder password protected

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, click 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?