Detecting Excel file is password protected

[aziz rasul]

Does anyone have any VBA code that would tell me whether an xlsb file is password protected?

 

[Bilbo_Baggins_Esq]

Did you ever find an answer to your question?

 

[aziz rasul]

Yes and No.

I did originally try the following code before posting but couldn't get the xlsb files to work, hence the thread. However now I have gone back to it, it now seems to work!

Public Function PasswordProtected(strFilename As String) As String
    
    Dim dbs As DAO.Database
    
    If Dir(strFilename) = "" Then
       PasswordProtected = "File does not exist"
       Exit Function
    End If
    
    If Right(strFilename, 3) = "mdb" Or Right(strFilename, 5) = "accdb" Then
        On Error GoTo ErrorHandler
        Set dbs = OpenDatabase(strFilename)
        PasswordProtected = "False"
        dbs.Close
        Exit Function
    ElseIf Right(strFilename, 3) = "xls" Or Right(strFilename, 4) = "xlsx" Or Right(strFilename, 4) = "xlsm" Or Right(strFilename, 4) = "xlsb" Then
        On Error GoTo ErrorHandler
        Set dbs = OpenDatabase(strFilename, True, False, "Excel 5.0")
        PasswordProtected = "False"
        dbs.Close
        Exit Function
    Else
        PasswordProtected = "File does not exist or incorrect extension"
        Exit Function
    End If
    
ErrorHandler:
    PasswordProtected = "True"
    Exit Function
    
End Function

Let me know if it works for you.

 

[Isskint]

Not sure if this would work on xlsb but for XLSX, XLSM set

On Error Resume Next

then use Workbooks.Open with a password

Workbooks.Open Filename:= XlToTest, Password:="zzzzzzzzzzzz"

and test for an error number. If there is an error you know its protected and can close the file

Workbooks(XlToTest).Close False

 

[Bilbo_Baggins_Esq]

Well, I guess that could work, but for simple password checking, all you really need to do is declare an Excel Workbook Object (Assuming you're working in Access) and then try to open the file.
Put an error handler in and either it opens or it errors out with Err.Number 1004 (password exists).

Checking the VBA Project for locked status is a little different.

 

[aziz rasul]

Both good suggestions.