• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to check if files in a folder have write password protection

Shawn Miller

New Member
Hello,

I am trying to run a macro that loops through files in a folder path and reports Yes/No if it has a write protect password. The script below works, but a user has to wait for each file to open and hit "esc" on the password dialog box if the file has a write protect password. I tried modifying the section that says Password:="zzzzzzzzzzzz" to be WriteResPassword:="zzzzzzzzzz" and when I did this the macro looped through without user interaction BUT it simply reported all files having a write protect password, even those some files did NOT have a write protect password.

Any suggestions on how to modify this or is there an easier way to check?

>>> use code - tags as written in Forum Rules <<<

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

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    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
    Else
        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
Loop

End Sub
 
Last edited by a moderator:
I had to do a little research, but in the end came up with the same solution you did: Supply a fake password, then trap the error if any. But when I ran your program as-is on a directory of my own, with multiple Excel workbooks (and I changed one to have a password on it), it didn't ask me for a password, and it correctly reported which had a password and which didn't. I have a different coding style, so I would have written it differently. But apparently yours worked correctly.

For what it's worth, I'm running Excel 2019 under Windows 10.
 
Hey there - thank you for the feedback. Maybe I goofed or maybe doing over VPN is acted different. After seeing your reply I tried again by changing to code to WriteResPassword:="zzzzzzzzzz" and I tried with local files and it appears to work. Thanks again!
 
Back
Top