Author: Oscar Cronquist Article last updated on July 29, 2017

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. But a macro can open workbooks in a folder, check if password protected and then close the workbook. One by one.

check if workbook is password protected

VBA Code

Sub CheckWbook()
Dim Value As String, a As Single

With Application.FileDialog(msoFileDialogFolderPicker)
    myfolder = .SelectedItems(1) & "\"
End With

Range("C4") = myfolder
Range("B7:C" & Rows.Count) = ""

a = 0
Value = Dir(myfolder)
Do Until Value = ""
    If Value = "." Or Value = ".." Then
        If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
            If Err.Number > 0 Then
                Range("C7").Offset(a, 0).Value = "Yes"
            End If
            Workbooks(Value).Close False
            On Error GoTo 0
            Range("B7").Offset(a, 0).Value = Value
            a = a + 1
        End If
    End If
    Value = Dir

End Sub

Download excel *.xlsm file

List password protected workbooks.xlsm