Can I check if the workbooks in a folder have a password to open.

I actually want to display a message saying that "abc.xls is protected" if
abc.xls has a password to open.

On Apr 21, 6:28 am, Amit <A...@discussions.microsoft.com> wrote:
> Can I check if the workbooks in a folder have a password to open.
>
> I actually want to display a message saying that "abc.xls is protected" if
> abc.xls has a password to open.

Good evening Amit,
You can use the following function to detect if a file is password
protected:

Function fn_IsPassWordProtected(strWbk As String) As Boolean
Dim cn As Object

Set cn = CreateObject("ADODB.Connection")

On Error Resume Next
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strWbk & "; Extended
Properties=Excel 8.0;"
.Open
If Err.Number <> 0 Then
If Err.Number = "-2147467259" & Err.Description = "Could
not decrypt file." Then
fn_IsPassWordProtected = True
End If
End If
.Close
End With
On Error GoTo 0

Set cn = Nothing
End Function


You can test this in the immediate window on a couple of files with
the following snippet:
?fn_IsPassWordProtected(application.GetOpenFilename("Excel Files
(*.xls),*.xls"))

I've tested this function on several password/non-protected files and
all password-protected files were able to raise that error and
description...but test it for additional scenarios which may have been
overlooked.

Have a good weekend,


Ray R. Gable, Jr.